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

calendar_today Asked Jun 24, 2010
thumb_up 7 upvotes
history Updated April 16, 2026

Question posted 2010 · +4 upvotes

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

Accepted answer +7 upvotes 

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

2 code variants in this answer

  • Variant 1 — 1 lines, starts with =IF(X2>=85,0.559,IF(X2>=80,0.327,IF(X2>=75,0.255,-1)))
  • Variant 2 — 10 lines, starts with =IF(X2>=85, 'If the value is in the highes…

Top excel Q&A (6)

+7 upvotes ranks this answer #122 out of 167 excel solutions on this site .
excel