Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple conditions and multiple return values | Excel Worksheet Functions | |||
HELP| populate cell with particular value based on multiple condit | Excel Worksheet Functions | |||
COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |