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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,118,698
Messages
5,573,696
Members
412,548
Latest member
wallisonlac
Top