Get A Result Based On Several Different Conditions
New to the forum and in need of a bit of help. Friend of mine is in here regularly looking for assistance and tells me this is the best excel forum around. Thought I would put it to the test with a problem that is doing my head in.
The following is a table which shows a salesmans sales. He or she has sold to 8 customers.
What I need to do is show in the commission column how much they should get paid for the additional products based on this criteria.
1. If the penetration is equal to or over 41%, and he has sold the additional product for max profit of 250 then he should get £40
2. If the penetration is less than 41%, and he has sold the additional product for max profit of 250 then he should get £25
3. If he hasnt sold the product for full amount then he gets 10% of the profit, regardless of the penetration ...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Conditional Formatting - More Than 3 Conditions - Result Based On Another Cell
I have established that to have more than 3 conditions in my version of Excel (2003) i have to use VBA. Unfortunately I have very little knowledge of this process. I have attached an example of the worksheet I am working on. The worksheet in question totals hours worked for payroll. problem: Required result: Cell to change to 1 of 8 background colours in response to selection of 1 of 8 options in drop down list in the cell directly next it. i.e. (in reference to attachment - sheet DATA) IF E6 = Holiday, then D6 = Red IF E6 = Half Day Holiday, then D6 = Red IF E6 = Sick, then D6 = Blue IF E6 = Sick Half Day, then D6 = Blue IF E6 = Bank Holiday, then D6 = Green IF E6 = Compassionate Leave, then D6 = Pink IF E6 = Unpaid Leave then, D6 = Yellow IF E6 = Unpaid Leave Half Day then, D6 = Yellow However, the same basic table as shown in sheet DATA appears several times on the worksheet. And in each instance the conditional formatting is the same (in reference to the relative cells).
View Replies!
View Related
Lookup Multiple Conditions To Return One Result
I am preparing an "automated" order form. I have a list of products (ten) in a pull down list, a list of options (3) in a pull down list and a list of the pricing. So, If PRODUCT_A with OPTION_A, then PRICE = $X. If PRODUCT_A with OPTION_B, then PRICE = $Y. If PRODUCT_B with OPTION_C, then PRICE = $Z. etc. I want the user to select the product, then the option, and have the correct price "pre-fill" the cell.
View Replies!
View Related
Result Based On Existance: If Coulmn Contains A Anywhere Then The Result Should Be A
I have 4 categories A, B, C & D. These are in desending importance, means A is most important and D is least important. Now there could be many A, B, C & Ds listed in a column. The challange is if coulmn contains A anywhere then the result should be A. If A is absent, then search for B, if present anywhere then display the result as B. It doesn't matter how many times A or any character is listed in column. I am attaching a sheet for better explanation.
View Replies!
View Related
Sum Multiple Conditions & Subtract Result
Attached is a section of a spreadsheet containing many columns and about 20000 rows..these rows are made up of events.. named in column a. In column F I want A FORMULA to print the difference between each rating(in col C) and rating of THE ROW identified by a 1 IN the favrank col(col e) in that event. I did the first event manually to show what I mean.
View Replies!
View Related
Lookup Based On Two Conditions ...
I know that there are bunches of threads concerning lookups with multiple criteria, but I just can't figure out how to translate one to my situation. I want to return a value based on an item name which is in column A, and an operation which is in row 1. The array from which I need to look up the value contains part number in column B, operation in column G, and the actual value I need returned in column H. So I need to return something like this: ...
View Replies!
View Related
Display Value Based On 2 Conditions
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course. The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
View Replies!
View Related
Lookup Based On A Few Conditions
I am wondering if I can do a formula that would tell me if the name that appears in column J or K appears more than once at the time/date slotted in columns A and B. Basically, I want to make sure that the name(s) in column J and K aren't assigned 2 different places at the same time slot (column B) on the same say (column A).
View Replies!
View Related
Lookup Based On 2 Conditions
In the sample that I have attached. I am trying to compare Control ID and Business Date in Sheet[Test] and Sheet[Perform], If they are the same, then populate in Sheet[Perform] Column Test Status with the corresponding row in Sheet[Test]. note that Control ID and Business Dates are not constants.
View Replies!
View Related
Sum Based On 2 Conditions
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime. Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)
View Replies!
View Related
Result Based On ComboBox Choice
I'm looking to set up a combo box with different year options and add functionality so the data that can be seen on the sheet at a given time is driven by the combo box year value. (functionality somewhat like a webpage, where once you choose a certain value from a drop down box, you see data corresponding to the value)
View Replies!
View Related
Return Result Based On Criteria
if Cell F42 contains 50%, then my fomula returns "wrong" =IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong"))))) how do I get the formula calculate correctly?
View Replies!
View Related
Result Based On Two Criteria Lookup
See the attached file. I think index and match may help me having done a search several times in these forums, but having been trying to do this for several hours I am just struggling to understand each part of the formula unfortunately. I have a list of data (cellsA1 to C33) which I need to use as my source for the information that is displayed in cell I2 based on the two selections made in cells F2 and G2. I can't use filter and data sort in this situation - I am creating a spreadsheet for someone who truly knows nothing about excel, so I need this to be as simple as pointing and clicking at cells F2 and G2 and the formula doing all other work. Is it possible to do what I am asking for (hopefully the fact that peoples' names are repeated and months are repeated won't cause a problem?
View Replies!
View Related
Summing Based On Multiple Conditions
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example: 1 A 1 1 B 2 2 A 3 2 B 4 3 A 5 3 B 6 I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
View Replies!
View Related
Cell Value Based On Multiple Conditions
I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10. I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.
View Replies!
View Related
Reset Cell Value Based On Two Conditions
I am trying to build a spreadsheet that tallies hours worked and hours available for the next day. The key to this spreadsheet is the number 70. A worker can accumulate no more then 70 hours in an 8 day period, however after any day with no hours worked the rule resets to 70 hours available again. I have attached a sample spreadsheet so you can see how this should work.
View Replies!
View Related
Sum Based On Multiple Conditions
I am trying to sum numbers based on three counditions. My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd* The yellow cell contains the formula I am using.
View Replies!
View Related
Selecting Values Based On Conditions
find a book with two sheets. One contains data and the other is the report that I must generate. In sheet one ull find the list of all the trucks received. I would like to see all trucks that I have received in the month of say June in the report sheet as per its arrival date when I change the month on the top of the report.
View Replies!
View Related
Extract From List Based On Conditions
I want to know how I can make Excel look at a list of information on one worksheet, extract certain items from the list, and place it on another workshet For example, the following list would be imported from an inventory program as text and pasted on worksheet 1: P+green beans d-2.99 c=2 cd+10258 p+cherry tomatoes d-3.99 c=1 cd+11204 p+red apples d-.69/lb c=3 cd+08902 p+tissue d-1.99 c-2 cd+20023 How can I have Excel take this information and create a list like the following on worksheet 2? ...
View Replies!
View Related
Calculate An If Value Based On A Number Of Conditions
I am trying to create on excel order form. I want customers to be able to input the item # (a range from 1 to 12), then I want the to price to be calculated based on the item # they input. For example. If they choose item #2 in A6 then the price in F6 will be recorded as $8.00. (the price would change for each item # they input). the formula I started out with was: =IF((A6=1),"$8.00") this worked for me if A6 did in fact equal 1. So I tried adding this equation to the formula. =IF((A6=1),"$8.00")*OR((A6=2),"$7.00")....this would continue on. I even pressed "command return" after the statement as if I was entering an array formula. I got the error #VALUE!
View Replies!
View Related
SUMIF Based On 2 Conditions From 2 Columns
Have had a search around the web forums for an answer to this and am not getting anywhere. In column A i have text - apr, may, jun etc In column E i have general format text - a 6 figure number in column M i have text - eg 886.97 (a currency, but in text format) i want to: where column a=apr and column E=212130 sum the contents in column M
View Replies!
View Related
Returning A Text Value Based On 2+ Conditions
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes. A B C E F G M S Get value from G M S 5G M T M T 6M M U M U 7H M V M V 8K
View Replies!
View Related
Average Based On Multiple Conditions
Im currently trying to report on cycle times for a certain task that is carried out on a daily basis. I have used NETWORKDAYS to calc each rows cycle time in Cell R14:R512 the code is:- =IF((NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)=0,"",(NETWORKDAYS(O14,N14)-1)*($AH$11-$AH$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),$AH$11,$AH$10),$AH$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),$AH$11,$AH$10)) This is returning the cycle time providing both the start date/time and end date/time are filled in, or it returns blank if nothing has been filled in. I have then tried to calc the average overall cycle time in Cell R12 by using the below formula = SUM(R14:R513) / (COUNT(R14:R513) - COUNTIF(R14:R513,0)) This seems to be working proved i have two rows of data filled in. My problem lies when all the cells are blank, it then returns the DIV error. I have tried using an if statement to ignore if the cell is blank but it still returns the DIV error. Im really at a loss on this and now im not even sure if i have gone about this in the right way or if there is a better way to approach the whole task? I have attached a sample workbook to try and better explain.
View Replies!
View Related
Extract Certain Data Based On Conditions
method to extract certain data from a column of values. The attached workbook has two sheets. From the worksheet named "Data" I would like to be able to extract any values >0.00% and then display the corresponding "model" (C:C) on the other sheet called "Results". I need to be able to achieve this for each day in the month and disregard all other values so I am left with a concise list. I have manually entered the info for the 1st June as an example.
View Replies!
View Related
Add Sheets Based On Conditions
i am not able to solve this problem. 1- I have 3 tabs in this worksheet with the data - Tabs A , Master Data and refernce Table 2- Suppose column K in the tab " Master Data " has a record or number, they can be repeated as the new data us added every month. 3- Reference table has the list of the same data record ( numbers ) . Now the situation is if there is a new record in the column K of the master data sheet the Macro should do the following. a- Check for the record in the reference table , if doesnt exist then add the record in the reference table b- create another sheet same as the sheet " A" (duplicate of A) and name it the new record number b- Plug the new record number in the cell B2 of the new sheet c - Copy the row 8 from ( D8 to O8 ) in the new sheet from (D8 to O8)from sheet A
View Replies!
View Related
Return Data Based On Two Conditions
I have very limited vb experience and I believe it’s the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice: Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc. Range U5 – U16 will be pre-populated with the line price for each month if the line quantity is less than 10 Range V5 – V16 will be pre-populated with the line price for each month if the line quantity is 10 or more The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just don’t have enough experience to write this myself.
View Replies!
View Related
Copy Range Based On Certain Conditions
I have this macro to copy a range to another worksheet: Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y ' las cajas ATR Dim rng1 As Range Dim rng2 As Range Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown)) Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown)) rng1.Copy Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True rng2.Copy Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False _ , Transpose:=True Application.CutCopyMode = True End Sub The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.
View Replies!
View Related
Drop Down List Based On The Result Of A Vlookup
I am trying to make a drop down list based on the result of a vlookup. What i want to do is look into a table that has country name, depot station, but i have more than one depot per country so when i look up with vlookup i only get one result back, the table looks like this. Country Country nameDepot code Depot name 1 GBUnited Kingdom STN Stansted 2 GBUnited Kingdom EDI Edinburgh 3 GB United Kingdom EMA East midlands 4 FR France GNO Garanoa 5 FR France MRS Marseille How can i look up GB or United Kingdom and get all the depots listed from that country.
View Replies!
View Related
Return Result Based On Multiple Criteria
I have been trying this for hours but to no avail. I have a table with 4 columns headers Name, Amount, Loc and Code The name may look like ABC 1, ABC 2.... The Loc may be in US, GB... and the Code may be AA, BB I need to return a result "Y" if the sum of the amount is > 100 and "N" if the sum of the amount < 100 based on the conditions of the following :if 1) Name is the same entity, such as ABC 1 and ABC 2 and 2) Loc is the same, US..and 3) Code is the same I have attached a sample to illustrates the result
View Replies!
View Related
Return Result Based On Time In Cell
I have a table of repair jobs done over a period of time. I just need to determine during which shift was the repair job done. The time during which the job was initiated is called Notification Time. If the job was done at, say, 0100 hrs, then it was done during Shift 1 (0000hrs to 0800 hrs) If done at 0830 hrs, Shift 2 (0801 to 1600hrs) If done at 2030 hrs, Shift 3 (1601 to 2359 hrs) I need a formula to allow me to determine the Shift no. by just checking against the Notification Time column.
View Replies!
View Related
Finding Best 5 Based On Result Value Without Exceeding 100
Each car has a value, and each car has odds. I must choose 5 cars, and the value cannot exceed 100. I typed in all my data, can Excel choose the best possible lineup for me? In the end, I need the lowest result possible while only using 100 pts in value. I am using Excel 2003 Here is a sample of my data. Car # Value Odds (#to1) (Result=Value x Odds) 123.5494.00 223.45117.00 322.85.75131.10 422.66.75152.55 522.212.5277.50 62315345.00 71523.5352.50 82217.5385.00 921.820436.00 1019.523.5458.25 1121.623.5507.60 122125525.00 1321.725542.50 141930570.00 1522.130663.00 1620.335710.50 1721.934744.60 182040800.00 1918.845846.00 2018.647.5883.50
View Replies!
View Related
Return Values Based On Lookup Result
I am currently looking at the workings of a spreadsheet designed by someone else. First of all i need to know,how these combo boxes are created in the attached sheet,because it cant be edited.in addition to tht in the coloured cell (F17)i'm trying to dereive a formula which is,if (C17 = doll "1"),but its not working.Please someone give me a solution.
View Replies!
View Related
Return Result Based On Text In Cell
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities. In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario: Warranty Replacement = 20 Insurance Claim = 20 Billing Issues = 15 Retention Opportunities = 20
View Replies!
View Related
Display Result Based On Time Difference
I want to make a formula based on 2 times ex: 10:07:00 and 10:09:00. This formula should display "intime" if the diference between both is under 20 minutes, "outime" if the diference is above 20 minutes and it should display "error" if the time is under 0 minutes (this will only happen when someone makes a mistake typing in the time. For example 10:37:00 and 10:36:00)
View Replies!
View Related
Return Text Based On Calculation Result
I need an excel function for this... Say, Cell A2= 8 Cell A3= 1 If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
View Replies!
View Related
Color Formula Result Based On Criteria
I would like to use formulas to display different results eg UNDERBUDGET - OK - OVERLIMIT etc. Ideally they would be shown using different colours & text size. I can't seem to change the colour/text size inside the formula for the different words - only for the whole cell.
View Replies!
View Related
Color Cells Based On Average Result
I have a cell with the following formula: =IF(ISERROR(AVERAGE(A2,C2,D2)),"",(AVERAGE(A2,C2,D2)). I'm using this formula because I want to average the selected cells, but if A2,C2, or D2 are blank I don't want it to return the # DIV/0! error, hence the "ISERROR" part. Now... I want to conditionally format the cell with the above formula in it so that if it is equal to or greater than 80% it's green. Here's the problem, the conditional format colors the cell green if it is blank too. How do I stop that? Below is an attached spreadsheet to show what I'm trying to do.
View Replies!
View Related
Flagging Duplicates Based On Multiple Conditions
Flagging Duplicates based on Multiple conditions.. I have attached the file for reference with Dummy Data as the actual data runs in thousands... What I require is to flag Duplicates with some formula so that I can filter and then later delete those specific rows from the records... The Duplications has to be considered on Multiple criterions: 1. If the FNAme and LName is appearing for one Company more than once. 2. Sometimes there would be inconsistency in Data Entry, so the FNAme would be typed in the Column for LName and vice-versa.The code needs to understand the same. 3. Alongwith the above, if the Designation is different then the record with the Lower Designation based on the Weightage should be Flagged as "Duplicate". Ex: - 4. If there's a tie between the same designation then the record with the Maximum information across all the columns having Max info should be Unique. Ex:- The amount of details mentioned in the following columns would determine the record to be termed as Duplicate or unique 5. If still the records are identical then the first occurence can be unique... I have made this data to give an example as the actual data is very confidential. Please allow room for adding New Designations as the Designation and their Weightage needs to be Dynamic. Please refere the Word doc Duplication Criterion Explanation for the explanation.
View Replies!
View Related
Trying To Use SUMIFS To Sum A Column Based On Two Conditions
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving. Here is my formula that works so far. =SUMIF(Data!C:C,"Office",Data!D:D) Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format. 1/9/2010 9:21:49 AM I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS. =SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates") Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.
View Replies!
View Related
Populate List Based On Two Selected Conditions
I have a worksheet which contains all the details of the medical equipment in the county that i'm responsible for. This worksheet posesses amongst others; two columns titled location and sublocation. I need a technique which will allow me to select a location from a dropdown list, select a sublocation again from a list and for these two conditions to then populate a list containing data such as asset code and description of all the items in the location and sublocation (possibly a little like an advanced filter, but only for the selected conditions) This list will then be used to provide an engineers report on equipment holdings at various locations. I'm open to any ideas fellas.
View Replies!
View Related
|
TRACKER
