`IF` statement with 3 possible answers each based on 3 different ranges
Solution 1
=IF(X2>=85,0.559,IF(X2>=80,0.327,IF(X2>=75,0.255,-1)))
Explanation:
=IF(X2>=85, 'If the value is in the highest bracket
0.559, 'Use the appropriate number
IF(X2>=80, 'Otherwise, if the number is in the next highest bracket
0.327, 'Use the appropriate number
IF(X2>=75, 'Otherwise, if the number is in the next highest bracket
0.255, 'Use the appropriate number
-1 'Otherwise, we're not in any of the ranges (Error)
)
)
)
Solution 2
You need to use the AND function for the multiple conditions:
=IF(AND(A2>=75, A2<=79),0.255,IF(AND(A2>=80, X2<=84),0.327,IF(A2>=85,0.559,0)))
Solution 3
Your formula should be of the form =IF(X2 >= 85,0.559,IF(X2 >= 80,0.327,IF(X2 >=75,0.255,0)))
. This simulates the ELSE-IF
operand Excel lacks. Your formulas were using two conditions in each, but the second parameter of the IF
formula is the value to use if the condition evaluates to true
. You can't chain conditions in that manner.
SQL-challenged
Updated on July 09, 2022Comments
-
SQL-challenged almost 2 years
I have 3 ranges of numbers and the answer depends on the range.
75-79=0.255 80-84=0.327 85+ =0.559
I tried to create an equation that accounts for the ranges, however Excel states that I have entered too many arguments for this function. Below is the equation that I entered that is not working. (X2 contains the number)
=IF(X2=75,X2<=79,0.255,IF(X2=80,X2<=84,0.327,IF(X2>=85,0.559,0)))
I also tried to enter the range of numbers into another sheet -
Age
, and got an error#Value!
.=IF(X2=Age!A1:A5,0.257,IF(X2=Age!A6:A10,0.327,IF(X2=Age!A11:A33,0.559,0)))