Formula for stepped rebate system

[GeorgeW5.5mm]

hi there, i have been given the task of setting up a sheet with a stepped rebate system. at the moment the page is blank apart from this small table, so 'sales' is in A1

Sales commission sub totals
0-100 0%
101-200 5%
201-300 7%
300> 10%

what i need it to do, is to separate the sales into the relevant rebate categories. for example, if we sold 500, it would split the first 100 to 0%, and so on. I have searched the forums and could only find one example but didnt know how to make it work for this.

Thanks

[joris moerings]

Hi,

created a short sample. Try this:

[GeorgeW5.5mm]

Thanks, that looks like it will do the trick, I will play around with it and see how it goes, I will report back when we have decided on if its ok.

cheers

[samba_ravi]

=SUMPRODUCT((A1>{100,200,300})*({0.05,0.02,0.03})*(A1-{100,200,300}))
Try this