`IF` statement with 3 possible answers each based on 3 different ranges

SQL-challenged picture SQL-challenged · Jun 24, 2010 · Viewed 252.6k times · Source

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

Answer

VeeArr picture VeeArr · Jun 24, 2010
=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)
             )
        )
   )