nested if

[his5r2m]

Hello all,

I know there is a really easy solution to this but I don't seem to have the intelligence required to resolve it!

In cell A1 I want a function that says:

If the value is less then 5'000 = 50
If the value is less then 10'000 and larger then or equal to 5'000 = 100
If the value is less then 20'000 and larger then or equal to 10'000 = 150
If the value is less then 30'000 and larger then or equal to 20'000 =250
Greater then 30'000 =400

Many thanks

[Richard Buttrey]

Hi,

I'm assuming you mean that the value you want to test is in A1. So one way is

=IF(A1<5000,50,IF(A1<10000,100,IF(A1<20000,150,IF(A1<30000,250,400))))

another option would be to use a VLOOKUP table.

HTH

[DonkeyOte]

To demo the lookup based approach Richard mentions (given it's scalable)

=IF(N(A1),LOOKUP(N(A1),{-9.99E+307,5000,10000,20000,30000},{50,100,150,250,400}),"")

[Blake 7]

@DonkeyOte. Hi Don, good morning.

If you dont mind taking the time to explain. I don't understand the use of the N value here - i have just read that this function converts a value to a number, but isn't A1 going to be a number anyway. I am still working out the translation into english of the rest of the formula.... i'm sorry to bother you with minutiae.... cheers Don. Hope you are keeping well

=IF(N(A1),LOOKUP(N(A1),{-9.99E+307,5000,10000,20000,30000},{50,100,150,250,400}),"")

[his5r2m]

Hello,

Can anyone tell me what is wrong with my formula?:

IF((F50:F109)<5000,50,IF((F50:F109)<10000,100,IF((F50:F109)<20000,150,IF((F50:F109)<30000,250,400))))

[teylyn]

this is an array formula and needs to be confirmed with Ctrl-Shift-Enter

[pr4t3ek]

your formula should be:

=IF(SUM(F50:F109)<5000,50,IF(SUM(F50:F109)<10000,100,IF(SUM(F50:F109)<20000,150,IF(SUM(F50:F109)<30000,250,400))))

[teylyn]

pr4,

why sum? Executed as an array formula, each cell gets evaluated individually. Quite a difference.

[pr4t3ek]

apologies, yes you are correct. i didn't exactly read his5r's question. array is correct. please ignore my formula

[teylyn]

Hello Blake 7

=if(n(a1), tests if A1 is a number. If it is text, the rest of the formula will not be executed and an error message caused by a text value will be avoided.

The second N(A1) is not really required, since it's already established that A1 is a number.

If A1 is a number stored as text, n(a1) will result in "false" and the rest of the formula will not be executed.

If you'd want the formula to execute with numbers stored as text as well as with real numbers, you'd need something like

=IF(ISNUMBER(A1+0),LOOKUP(A1+0,{-9.99E+307,5000,10000,20000,30000},{50,100,150,250,400}),"")

[Blake 7]

Thanks Teylyn for your explanation. Kind of you to take the time. I understand now how the N has been used aswell as the difference between N and isnumber so thanks alot.

Hope all well with you.

[teylyn]

Blake, the {} are used to define an array.

{1,2,3} is an array of three cells in a column, like
1
2
3


{1;2;3} is an array of three cells in a row, like

1 | 2 | 3

(where the | sign is the cell border )

and

{111,222,333;444,555,666;777,888,999} is an array of 9 cells arranged in 3 columns and 3 rows.

[Blake 7]

Thanks so much. This is wunderschon! forgive me, I don't have the umlaut....

Theres so much to learnnnnnn!!! argghhhhhhhhhh

Have a good day