Need help creating an IF statement with 3 possible answers, each based on a different range of data

Question

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 in 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 get 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)))

Any help would be greatly appreciated.

Answer 1

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.

Answer 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)))

Answer 3

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