Allocate rebates formula

[Titian]

The number of transactions is scaled down to provide this example.

Rebate scheme (Cum Sales = Cumulative sales) based on the total turnover of a group of customers.

Cum Sales....Rate....Sales Band...Rebate
20,000........1.0%.......20,000........200
35,000........2.0%.......15,000........300
45,000........2.5%.........3,400.........85
...................Total......38,400.......585 average rebate 1.5%

I would like to correctly allocate the £585 rebate across the individual customers who have generated the turnover i.e. NOT by using the simple 1.5%.

Clearly the large sales value customers will have earned a higher % rebate than the lower sales value customers - but how to calculate?

Customer....Sales value...Rebate
A...................15,000
B.......................900
C....................7,000
D....................3,000
E.......................500
F.....................9,000
G.....................2,000
H.....................1,000
Total...............38,400.........585

Your help is appreciaated.

 

[Titian]

Ah! yes I see.

I can see how someone may become fixated playing fruit machines, just watching the numbers fly round as the macro executes on this test dataset is "exciting".

I know that I can suspend screen output to speed up the program. Do you have anything else in mind to increase the speed?

Thanks.

 

[Jubjab]

The only way (apart from stopping screenupdating) is to reduce the number of iterations. Based on your actual data, you might:

* increase the number to add from 1 to something larger
* start off with a minimum value (instead of from 0, in the test example 500 would best)
* paste the number to add to all rows at once using pastespecial instead of looping through all rows

All the above requires checks to make sure that the numbers don't overflow, which makes things a more complicated.

It feels like there instead of making large amounts of code should be some easier solution. Maybe someone else has a better approach?

 

[Titian]

Thanks for coming back with your suggestions, I'll give them a try.
Regards