How do I set up a multiple condition formula?

[JoeAPM]

I have 12 different conditions where I need to assign a number:
If E6 < 36.9, then the number is 0
If E6 >=37 and <=37.49 then number is 0.25
If E6 >=37.5 and <=37.99 then number is 0.50
E6 >=38 and <= 38.49 then number is .75

and so on....

A friend told me that Excel will not let you use more than 7 conditions. Is
this true?

If someone could send me the formula to set up these conditions I would be
greatly appreciative as I have worn out the help tool!

Thanks!

Joe

 

[Don Guillett]

Just start with the highest number and work your way down
if(a2>10,1,if(a2>9,2,if(a2>8,3)))

 

[Shane Devenshire]

Hi,

First, if you are using Excel 2003 or earlier you are limited to 7 level
nested ifs. In 2007 that would be 64 levels.
Second, you can beat these limitations by concatenating the ifs in some
cases for example
=IF(E6 < 36.9,0,"")&IF(AND(E6>=37,E6<=37.49),0.25,"")&....
However, a VLOOKUP function is probably a better choice, set up a lookup
table such as:
F G
0 0
37 0.25
37.5 0.5
38 0.75

Then use the formula
=VLOOKUP(E6,F$1:G$12,2)

If you can have negative number in E6 just change the 0 in F1 to a large
negative number.

 

[RagDyeR]

You can set up a datalist in an out-of-the-way location, and make it as
large as you wish, and then use Lookup().

Expanding your example to match your scenario of 12 conditions, try this:

In Y1 to Z13, enter this:

Y Z
0 0
37 0.25
37.5 0.5
38 0.75
38.5 1
39 1.25
39.5 1.5
40 1.75
40.5 2
41 2.25
41.5 2.5
42 2.75
42.5 3

Now, you can use this formula:

=LOOKUP(E6,Y1:Z13)

Negative numbers in E6 will return an error.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


I have 12 different conditions where I need to assign a number:
If E6 < 36.9, then the number is 0
If E6 >=37 and <=37.49 then number is 0.25
If E6 >=37.5 and <=37.99 then number is 0.50
E6 >=38 and <= 38.49 then number is .75

and so on....

A friend told me that Excel will not let you use more than 7 conditions. Is
this true?

If someone could send me the formula to set up these conditions I would be
greatly appreciative as I have worn out the help tool!

Thanks!

Joe

 

[David Biddulph]

=IF(E6<37,0,INT((E6-36.5)/0.5)*0.25)

Note that you haven't specied the outcome for values between 36.9 and 37, or
between 37.49 and 37.5, and so on, but I have assumed that where you say
<=37.49 you really meant <37.5, and so on.