Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement help
I have a Dependent validation data that when the dependent is chosen it
populates cost in a cell example: B1-type of class (basic,standard,advanced) C1-list of classes per type selected D1-cost of class in C1 Choices Basic has 10 classes: 7= $100, 2 = 150 and 1= 200.00 Standard has 4 classes all cost 1600.00 Advanced has 6 classes all cost 1600.00 My IF statement doesn't work since there is more that 7 What next?!?!?! - Thanks again, Herz and His |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement help
Since the price is the same for class types of standard and advanced all you
need to do is test cell B1 for those types to get the price. You'd need to create a 2 column table for the basic class type. In the left column of this table you'd list the class names/designations and in the right column you'd list the corresponding price. Then your formula might look like this: =IF(OR(B1={"Standard","Advanced"}),1600,IF(AND(B1= "Basic",ISNA(MATCH(C1,H2:I11,0))),"",IF(B1="Basic" ,VLOOKUP(C1,H2:I11,2,0),""))) Where H2:I11 = the 2 column table for the basic classes. -- Biff Microsoft Excel MVP "HERZHIS" wrote in message ... I have a Dependent validation data that when the dependent is chosen it populates cost in a cell example: B1-type of class (basic,standard,advanced) C1-list of classes per type selected D1-cost of class in C1 Choices Basic has 10 classes: 7= $100, 2 = 150 and 1= 200.00 Standard has 4 classes all cost 1600.00 Advanced has 6 classes all cost 1600.00 My IF statement doesn't work since there is more that 7 What next?!?!?! - Thanks again, Herz and His |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statement help
Herz,
I would use the VLOOKUP function with a little trick to allow it for search with two arguments. First, I would make a table with all the possible combinations, in your case 10+4+6 rows. Let's say you have Type in column A, class in column B, then add a calculated column in C with the formula in C2: = A2&B2, then column D with the cost. Then, in the area where you want to automatically display the cost, use the formula: =VLOOKUP(F8&G8,C2:D19,2,0), where F8 is the cell containing the type and G8 the cell containing the class. C2:D19 represents the range with Type & Class, costs. Stephane. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
IF STATEMENT | Excel Worksheet Functions | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |