Using IF Function

[VC]

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

 

[Guest]

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 require:
=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

 

[Harald Staff]

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:

=(A1>2500)+(A1>5000)+(A1>10000)+(A1>20000)+(A1>35000)+(A1>50000)+(A1>70000)+(A1>100000)

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
=(A1>2500)*5%+(A1>5000)*5%+(A1>10000)*10%

HTH. Best wishes Harald

 

[Kassie]

You can also split IF statements over several columns, to overcome this
so-called limitation.

Try
=IF(A1>250000,"K",IF(A1>150000,"J",IF(A1>100000,"I",IF(A1>70000,"H",IF(A1>50000,"G",IF(A1>35000,"F","NOT")))))) in B1.
In C1 enter
=IF(B1<>"NOT",B1,IF(A1>20000,"E",IF(A1>10000,"D",IF(A1>5000,"C",IF(A1>2500,"B","A")))))

You can add many more columns if you wish

 

[Franz]

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.

Not seven nested IF, but seven *level* of nested IF... ;-)
For example you can use this function I found somwhere on the net with up to
26 nested IF:

=IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"),IF(B2="a",1,IF(B2="b",2,IF(B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2="g",B2="h",B2="i",B2="j",B2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9,IF(B2="j",10,IF(B2="k",11,12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B2="r"),IF(B2="m",13,IF(B2="n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18))))),IF(OR(B2="s",B2="t",B2="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B2="u",21,IF(B2="v",22,23)))),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error
- type a letter")))))))

If you copy this function in a cell, say C2, and you type a letter from A to
Z in B2, in C2 you'll have the position of the typed letter in the alphabet.

I think you can adapt this function to your needs.


--
Hoping to be helpful...

Regards

Franz

 

[Alex Andronov]

Of course a simple modification to your first function would give you the
whole answer:

=CHAR(65+(A1>2500)+(A1>5000)+(A1>10000)+(A1>20000)+(A1>35000)+(A1>50000)+(A1>70000)+(A1>100000))

Alex.

 

[Harlan Grove]

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:

=(A1>2500)+(A1>5000)+(A1>10000)+(A1>20000)+(A1>35000)+(A1>50000)
+(A1>70000)+(A1>100000)

....

You could, but why would you want to use it rather than

=SUMPRODUCT(--(A1>{2500;5000;10000;20000;35000;50000;70000;100000}))

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.

 

[Harald Staff]

You could, but why would you want to use it rather than

=SUMPRODUCT(--(A1>{2500;5000;10000;20000;35000;50000;70000;100000}))

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