Forum Discussion

Deleted's avatar
Deleted
Dec 15, 2017

calculating a sliding scale percentage based on value of a sum

for one of my customers I need to return a rebate on sales.  however we have two sales funnels to consider and the rebate amount is a sliding scale based on total sales. I need to sum 2 cells and depending on the sum value calculate the percentage accordingly

 

  • so if the sum of these two cells is greater than $500,000 but less than $1,000,000 i need to calculate at 5%
  • if the sum of these two cells is great than $1,000,000 but less than $2,000,000 I need to calculate at 6%
  • and if the sum is greater than $2,000,000 I need to calculate at 7%

 

i think I need to use an IF formula but am really struggling to create this to avoid doing it manually each time

Formulas and Functions
  • Mark Fitzgerald's avatar
    Mark Fitzgerald
    Iron Contributor
    Dec 19, 2017

    This can be done easily without hard-coding percentage breaks into nested IF statements by setting up a table defining the rates payable for various sales volumes.  Something like this:

     

     

    Which I named Rebates.

     

    Excel tables allow for structured referencing within formulas.  This means you can use the table and column names when typing formulas by typing "r" (without the quotes) and excel will suggest functions, ranges and table names beginning with R or r.  Continue typing the name or select from the dropdown to accept "Rebates".  Type a left square bracket "["and you will see a list of columns in the Rebates table which you can select from and close with a right square bracket "]".

     

    INDEX/MATCH formulas are a bit like VLOOKUP but more flexible in that you can return values to the left of your MATCH and specify exact (0), less than (-1) or greater than (1).

     

    The formula in column B below calculates the rebate percentage applicable for the sales volume and multiplies it be the sales volume.

     

     

    Much easier to maintain than nested IFs.

    • Deleted's avatar
      Deleted
      Dec 19, 2017

      Nicely done, you've used Match to specify the inequality you want and made it scale to any number of rebate tiers. This thread is full of great answers and neat tricks.

  • Erik Gardien's avatar
    Erik Gardien
    Copper Contributor
    Dec 16, 2017
    Cell1   Cell2   Sum   Rebate
    C11   D11   E11   F11
    400000 + 500000 = 900000 ==> 45000

     

    copy in cell F11: 

    =IF(E11>2000000;(E11*7%);IF(E11>1000000;(E11*6%);(IF(E11>500000;(E11*5%);0))))

     

    [This formula works only if your language settings are US or UK if not, you need to replace the IF function with you local formula function eg. for the Netherlands I need to replace IF with ALS than the formula will look like =ALS(E11>2000000;(E11*7%);ALS(E11>1000000;(E11*6%);(ALS(E11>500000;(E11*5%);0))))  ]

  • Sotannde Abiodun Kabir's avatar
    Sotannde Abiodun Kabir
    Copper Contributor
    Dec 16, 2017
    I don't have full opportunity, to work through this now. If it'll not be too late by six to eight hours from now, I'll definitely get it done. But just as a hint, try to use IFS instead of IF function. The IFS function determines the values between two conditions. Till next time, best regards.