Help with excel and IF statements

Mr._OCaz

Just trying to build something into a spreadsheet i am working and what i want it to do is if a certain value is a cell say 'A2' the formula i want in a particular cell say 'A10' is if the value in A2 is between 500 & 7500 i want value in A10 to be 36, and if the value is between 7501 and 25000 in A2 i want value in A10 to 54 and so on want this for about 8 different between values.

looked at it so much it is driving me crazy! Can anyone give me the formula to do this please? As only used very simple IF statements with excel before and i am self taught.

Thanks in advance

tru

A couple of helpful replies HERE

Chippy_Minton

=IF(A2<500,"",IF(A2<=7500,36,IF(A2<=25000,54,IF(A2<=27000,66,IF(A2<=28000,67)))))

and I've included a couple more ranges. However the formula gets a bit unwieldy with more than 4 or 5 IF statements so consider VLOOKUP instead.

Edit: corrected formula

Stompa

You could try using HLOOKUP, something like:

=HLOOKUP(A2,[0,500,7501,10000;1,36,54,123},2)

You'll notice that the array part of this:

[0,500,7501,10000;1,36,54,123}

is split into two parts by a semicolon, so:

0,500,7501,10000 and 1,36,54,123

When the value in A2 is >0 and <=500 (the first pair of values in the first part), it'll return 1 (the first value in the second part).
When the value in A2 is >500 and <=7501 (the second pair of values in the first part), it'll return 36 (the second value in the second part).
etc.....

Just adjust the values in the array to suit your requirements.

IMPORTANT: Where I've used a left square bracket [ above, you should use a left curly bracket instead (this board won't let me write a left curly bracket for some reason).

Stompa

[quote=Chippy_MintonHowever_the_formula_gets_a_bit_unwieldy_with_more_than_4_or_5_IF_statements_so_consider_VLOOKUP_instead.[/quote]

You're right, VLOOKUP would probably be more appropriate than HLOOKUP in this instance, in which case the equivalent of my HLOOKUP version becomes:

=VLOOKUP(A2,[0,1;500,36;7501,54;10000,123},2)

Fairwinds

The maximum number of ifs that you can nest is 7, so you may need to go down the lookup table route, which is a better option anyway (especially if the range values are likely to change)