How do I return a value based on multiple possible conditions?
 

I am trying to assign a specific value to a cell based on multiple
choices.

For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
This value is based on what is contained in cell B1.

If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"

I've tried a number of different formulas, but can't make it work with
=IF alone, since it only returns one of two values based on a single
condition.

How do I make the formula return one of four values based on four
different conditions?

I have to have this on another desk by tomorrow morning! Any help
anybody can give would be much appreciated, and then some! Thanks in
advance,

Mike Simard

How do I return a value based on multiple possible conditions?
 

Try this:

=IF(B1=12,"A",IF(B120,"B",IF(B128,"C",IF(AND(B1 39,B1<=52),"D",""))))

Note that this formula returns a blank if B1 is less than 12 or greater than
52.

HTH,
Elkar


"nevermore627" wrote:

I am trying to assign a specific value to a cell based on multiple
choices.

For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
This value is based on what is contained in cell B1.

If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"

I've tried a number of different formulas, but can't make it work with
=IF alone, since it only returns one of two values based on a single
condition.

How do I make the formula return one of four values based on four
different conditions?

I have to have this on another desk by tomorrow morning! Any help
anybody can give would be much appreciated, and then some! Thanks in
advance,

Mike Simard

How do I return a value based on multiple possible conditions?
 

Thanks Elkar - at least it's not returning an error - but regardless of
the value in B1, the formula always returns "A" to A1 - as if it stops
as soon as it returns the first "true" value.

How do I return a value based on multiple possible conditions?
 

Oops, wasn't thinking clearly when I wrote this. I got it backwards. Try
this instead:

=IF(B152,"",IF(B139,"D",IF(B128,"C",IF(B120,"B ",IF(B1=12,"A","")))))

Ugh, must be qutting time...

HTH,
Elkar


"Elkar" wrote:

Try this:

=IF(B1=12,"A",IF(B120,"B",IF(B128,"C",IF(AND(B1 39,B1<=52),"D",""))))

Note that this formula returns a blank if B1 is less than 12 or greater than
52.

HTH,
Elkar


"nevermore627" wrote:

I am trying to assign a specific value to a cell based on multiple
choices.

For example, cell A1 needs to be "A", "B", "C" or "D" (pay grades)
This value is based on what is contained in cell B1.

If the value in B1 is between 12.00 and 20.00, A1 needs to = "A"
If the value in B1 is between 20.01 and 28.00, A1 needs to = "B"
If the value in B1 is between 28.01 and 39.00, A1 needs to = "C"
If the value in B1 is between 39.01 and 52.00, A1 needs to = "D"

I've tried a number of different formulas, but can't make it work with
=IF alone, since it only returns one of two values based on a single
condition.

How do I make the formula return one of four values based on four
different conditions?

I have to have this on another desk by tomorrow morning! Any help
anybody can give would be much appreciated, and then some! Thanks in
advance,

Mike Simard

How do I return a value based on multiple possible conditions?
 

Elkar - I tried your formula in reverse, starting with the higher
numbers so that the condition would continue to be false until it hit
the right range, and BINGO! It worked like a charm! My gratitude is
virtually infinite - thanks a million! :)