ExcelBanter - Using IF Function

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Using IF Function (https://www.excelbanter.com/excel-worksheet-functions/25468-using-if-function.html)

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

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

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:

=(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

Kassie

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

Franz

Alex Andronov

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

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:

=(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.

Harald Staff

"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

All times are GMT +1. The time now is 04:35 AM.