Search code examples
excelif-statementworksheet-function

How to write a step function using IF functions


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 by using nested IF functions, but 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)))

Solution

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