Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using IF Function
I understand that up to seven IF functions can be nested as value_if_true and
value_if_false arguments to construct more elaborate tests. What should I do or use if more IF functions need to be nested? Eg. Greater than 2501 A From 2501 to 5000 B From 5001 to 10000 C From 10001 to 20000 D From 20001 to 35000 E From 35001 to 50000 F From 50001 to 70000 G From 70001 to 100000 H From 100001 to 150000 I From 150001 to 250000 J Above 250001 K |
#2
|
|||
|
|||
Hi
You could use a lookup table similar to this. 0 A 2501 B 5001 C 10001 D etc You can then use a VLOOKUP formula to return the grade you requi =VLOOKUP(YourValue eg 3500,TableRange eg A2:B:12,2) Something like that should fill the bill. his might help you further: http://www.cpearson.com/excel/excelF.htm#Grades -- Andy. "VC" wrote in message ... I understand that up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. What should I do or use if more IF functions need to be nested? Eg. Greater than 2501 A From 2501 to 5000 B From 5001 to 10000 C From 10001 to 20000 D From 20001 to 35000 E From 35001 to 50000 F From 50001 to 70000 G From 70001 to 100000 H From 100001 to 150000 I From 150001 to 250000 J Above 250001 K |
#3
|
|||
|
|||
Adding to Andy:
If your goal is some kind of numeric calculation, not simple text, then you can also use a technique similar to this: =(A12500)+(A15000)+(A110000)+(A120000)+(A1350 00)+(A150000)+(A170000)+(A1100000) as is it adds one for every true starement. Used in a setting like Amount Discount From 2501 to 5000 5% From 5001 to 10000 10% From 10001 to 20000 20% discount is =(A12500)*5%+(A15000)*5%+(A110000)*10% HTH. Best wishes Harald "VC" skrev i melding ... I understand that up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. What should I do or use if more IF functions need to be nested? Eg. Greater than 2501 A From 2501 to 5000 B From 5001 to 10000 C From 10001 to 20000 D From 20001 to 35000 E From 35001 to 50000 F From 50001 to 70000 G From 70001 to 100000 H From 100001 to 150000 I From 150001 to 250000 J Above 250001 K |
#4
|
|||
|
|||
You can also split IF statements over several columns, to overcome this
so-called limitation. Try =IF(A1250000,"K",IF(A1150000,"J",IF(A1100000,"I ",IF(A170000,"H",IF(A150000,"G",IF(A135000,"F", "NOT")))))) in B1. In C1 enter =IF(B1<"NOT",B1,IF(A120000,"E",IF(A110000,"D",I F(A15000,"C",IF(A12500,"B","A"))))) You can add many more columns if you wish "VC" wrote: I understand that up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. What should I do or use if more IF functions need to be nested? Eg. Greater than 2501 A From 2501 to 5000 B From 5001 to 10000 C From 10001 to 20000 D From 20001 to 35000 E From 35001 to 50000 F From 50001 to 70000 G From 70001 to 100000 H From 100001 to 150000 I From 150001 to 250000 J Above 250001 K |
#6
|
|||
|
|||
Of course a simple modification to your first function would give you the
whole answer: =CHAR(65+(A12500)+(A15000)+(A110000)+(A120000) +(A135000)+(A150000)+(A170000)+(A1100000)) Alex. "Harald Staff" wrote: Adding to Andy: If your goal is some kind of numeric calculation, not simple text, then you can also use a technique similar to this: =(A12500)+(A15000)+(A110000)+(A120000)+(A1350 00)+(A150000)+(A170000)+(A1100000) as is it adds one for every true starement. Used in a setting like Amount Discount From 2501 to 5000 5% From 5001 to 10000 10% From 10001 to 20000 20% discount is =(A12500)*5%+(A15000)*5%+(A110000)*10% HTH. Best wishes Harald "VC" skrev i melding ... I understand that up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. What should I do or use if more IF functions need to be nested? Eg. Greater than 2501 A From 2501 to 5000 B From 5001 to 10000 C From 10001 to 20000 D From 20001 to 35000 E From 35001 to 50000 F From 50001 to 70000 G From 70001 to 100000 H From 100001 to 150000 I From 150001 to 250000 J Above 250001 K |
#7
|
|||
|
|||
Harald Staff wrote...
If your goal is some kind of numeric calculation, not simple text, then you can also use a technique similar to this: =(A12500)+(A15000)+(A110000)+(A120000)+(A135 000)+(A150000) +(A170000)+(A1100000) .... You could, but why would you want to use it rather than =SUMPRODUCT(--(A1{2500;5000;10000;20000;35000;50000;70000;10000 0})) Any time you repeat the same cell reference more than 3 times the odds become quite high that there's a more compact way to do what you're trying to do. |
#8
|
|||
|
|||
"Harlan Grove" skrev i melding
oups.com... You could, but why would you want to use it rather than =SUMPRODUCT(--(A1{2500;5000;10000;20000;35000;50000;70000;10000 0})) Hi Harlan I always needed to treat each TRUE differently, as in the discount sample I posted. This posting was for educational purposes though, I think one must understand my solution to be able to understand its Sumproduct equivalent.. Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clock | Excel Worksheet Functions | |||
Need a ISWorkday Function -- Any Ideas | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |