If AND OR nested Function with multiple answers

Question

I having issues with a fairly complex nested IF/AND/OR formula to manage a sales incentive where there is a payout gate and multiple payout caps.

Rules are:

• Salesrep earn 1$ for every product sold if his/her “rate” is over 30%.
• Payout is capped at 250$ if Rate is between 30-35%
• Payout is capped at 350$ if Rate is between 35-40%
• Payout is capped at 500$ if Rate is between 40-45%,
• Payout is capped at 750$ if Rate is over 45%

The picture shows a basic example of an excel sheet to give you a clearer idea of what I am trying to do. I just can't figure out the formula to fill in columns E2:E5 in yellow.

Any help would be more than helpful. I've tried separating out the formulas into several extra columns, but just can't get to the right end result. Many thanks.

Answer 1

You have to put this on cell E2:

=IF(C2>0.3,IF(C2<=0.35,250,IF(C2<=0.4,350,IF(C2<=0.45,500,IF(C2>0.45,750,0)))),0)

An explanation:

If C2>0.3 then
    If C2<=0.35 then
          250
    Else
          If C2<=0.4 then
               350
          Else
               If C2 <=0.45 then
                     500
               Else
                     If C2>0.45 then
                          750
                     Else
                           0
                     End If
               End If
          End If
    End If
Else
    0
End If

On cell F2 put: =IF(D2>E2,E2,D2) and that's the final result.

Answer 2

You can do this without a lot of complicated nesting using MIN, INDEX, and MATCH. Place the following in E2 and fill down.

=MIN(D2,INDEX({0,250,350,500,750},MATCH(C2,{0,0.3,0.35,0.4,0.45},1)))

How this works:

MATCH(C2,{0,0.3,0.35,0.4,0.45},1) compares the percentage in C2 to the array of lower bounds for the payout cap ranges. The MATCH function will return where in the array C2 falls. For example, if C2 is 10%, the function will find that 10% is between 0% and 30%, so it will return 1 because it falls in the first range. If C2 were 33%, it would return 2 because 33% falls in the second range, between 30% and 35%.

The value returned by the MATCH function is then used by the INDEX function to return a value from the same position in the array of payout caps. So, for example, if MATCH returns 1, then INDEX will return the first number from the array, 0. If MATCH returns 4, INDEX will return the fourth number from the array, 500. Together, MATCH and INDEX work like a lookup table.

The last step is the MIN function, which compares the uncapped value in D2 to the cap returned by the INDEX function. It returns the lesser of the two values, just as the cap rule dictates.

Answer 3

Like most things in Excel, there's more than one way to tackle a problem. You asked specifically about solving it with IF logic, and jcbermu's answer does that. Another method is to do it with a lookup table:

It gives you a simpler formula for this kind of problem. The formula in E2, which you can copy down the column as needed, is:

=MIN(B2,VLOOKUP(C2,G$2:H$6,2))

Instead of defining min and max of each range, you would see how the rate compares to the minimum of each range. Rates below the minimum qualifying 30% have a cap of $0.

VLOOKUP finds the largest rate in the table that doesn't exceed the value in column C and returns the associated cap. The MIN function returns the smaller of the raw payout calculation (since that's 1 times the column B value, I'm just using the column B value), or the cap.