Calculating rebate bands based on total sales

GarryFreeman

New Member
Joined
Apr 17, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I am hoping that someone can help me with this.
In the image below you can see that I am trying to reward based on increased purchases, however this agreement is not a back to zero agreement.
In the example I am trying to reward the amount spent in a particular band between the 2 parameters. For example the difference between 95,000 and 99,999 is £4,999 which then attracts a payment of 249.95 (4,999 x 5%)
However my formula fails when the final total spend is between the bands as you can see in G24. What I need to show is £102,000 x 10%. Then M6 sums column I
If the T/O was for example £77,000 then this would attract all the cash payments in column I above row 14, add £2000 of T/O at 5% and everything below would remain blank "" with no reference to FALSE if possible

I thank you in advance for your help

Garry

1606216169161.png
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,509
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What is the correct not rounded total for 64,999.50?
What is the correct not rounded total for 104,999?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,509
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your example is not clear. If you have clear brackets and rates one calculation can provide the amount for the rebate.
Can you manually complete the arithmetic for 64,999.50? What is the rebate?
or
Review and edit the following
B3 names the ranges. With B3 you would not have to leave the table of brackets and rates on the sheet.
Column H is not required; it just shows the arithmetic.

T202011b.xlsm
ABCDEFGH
1Sales102,000.00BracketRateRate Diff
2Rebate2,400.000.001%1%600.00
32,400.0060,000.002%1%100.00
465,000.003%1%150.00
570,000.004%1%200.00
675,000.005%1%1,350.00
72,400.00
4a
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>D2:D6),B1-D2:D6,F2:F6)
B3B3=SUMPRODUCT(--(B1>aB),B1-aB,aR)
F2:F6F2=E2-N(E1)
H2:H6H2=(MIN($B$1,D3)-D2)*E2
H7H7=SUM(H2:H6)
 

GarryFreeman

New Member
Joined
Apr 17, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the correct not rounded total for 64,999.50?
What is the correct not rounded total for 104,999?

Your example is not clear. If you have clear brackets and rates one calculation can provide the amount for the rebate.
Can you manually complete the arithmetic for 64,999.50? What is the rebate?
or
Review and edit the following
B3 names the ranges. With B3 you would not have to leave the table of brackets and rates on the sheet.
Column H is not required; it just shows the arithmetic.

T202011b.xlsm
ABCDEFGH
1Sales102,000.00BracketRateRate Diff
2Rebate2,400.000.001%1%600.00
32,400.0060,000.002%1%100.00
465,000.003%1%150.00
570,000.004%1%200.00
675,000.005%1%1,350.00
72,400.00
4a
Cell Formulas
RangeFormula
B2B2=SUMPRODUCT(--(B1>D2:D6),B1-D2:D6,F2:F6)
B3B3=SUMPRODUCT(--(B1>aB),B1-aB,aR)
F2:F6F2=E2-N(E1)
H2:H6H2=(MIN($B$1,D3)-D2)*E2
H7H7=SUM(H2:H6)
Thanks for you patience Dave..

Below is what I am trying to achieve, I am trying to get to a formula that will sum the correct totals based on the figure in D4 (120000 in this instance.
Using the first example below every band up to row 24 achieves the full amount of turnover applied at the relevant rate. However on line 26 it only achieves 2000.

In the second example the same can be said but only up to line 42 where it achieves 5% of 1000.
I am looking for something that will auto populate every band based on the total t/o.

Hope this is clearer?

1606229542604.png
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,509
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I provided a suggestion on how you can complete the calculation with one formula.
Why do you want separate calculations for each band?
I did include the arithmetic for each band to show that the formula works.
I also asked for the calculation for 2 different amounts to have a correct target.

I apologize for thinking that you required a calculation for amount of rebate for a specified amount.

Perhaps someone else will follow your example.
 

GarryFreeman

New Member
Joined
Apr 17, 2020
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I provided a suggestion on how you can complete the calculation with one formula.
Why do you want separate calculations for each band?
I did include the arithmetic for each band to show that the formula works.
I also asked for the calculation for 2 different amounts to have a correct target.

I apologize for thinking that you required a calculation for amount of rebate for a specified amount.

Perhaps someone else will follow your example.
Thanks for your help Dave

Unfortunately I need it split out by bands to demonstrate how the total figure was calculated.
Clearly this can be done manual was just looking for a way to automate the process as I have many accounts to provide this detail to.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,509
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You can copy post #4 see the icon below the f(x). Paste it into a clean sheet.
You can review the formulas. The XL2BB did not copy the named data so ignore that formula.
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,509
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
T202011b.xlsm
ABCDEF
1Sales102,000.00BracketRate
2
3Rebate2,400.000.001%600.00
460,000.002%100.00
565,000.003%150.00
670,000.004%200.00
775,000.005%1,350.00
81E+3082,400.00
9
4a
Cell Formulas
RangeFormula
B3B3=SUMPRODUCT(--(B1>D3:D7),B1-D3:D7,E3:E7-E2:E6)
F3:F7F3=(MIN($B$1,D4)-D3)*E3
F8F8=SUM(F3:F7)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,814
Messages
5,574,487
Members
412,597
Latest member
Timtec
Top