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

excel if-statement worksheet-function
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)
             )
        )
   )

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Nested IF statements in Excel [Over the 7 allowed limit]

I am trying to create a spreadsheet which automagically gives a grade to a student based on their marks they got. I've apparently hit Excel's nested IF statement limit which is 7. Here's my if statement: =IF(O5>0.895,"A+",IF(…

Read More
If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More