ExcelBanter - How do I set up a multiple condition formula?

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

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

- - How do I set up a multiple condition formula? (https://www.excelbanter.com/excel-worksheet-functions/221623-how-do-i-set-up-multiple-condition-formula.html)

How do I set up a multiple condition formula?

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

How do I set up a multiple condition formula?

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"JoeAPM" wrote:

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

How do I set up a multiple condition formula?

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

"JoeAPM" wrote in message
...
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

How do I set up a multiple condition formula?

=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.
--
David Biddulph

JoeAPM wrote:
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