Working out accruing %'s in excel

Clairecat

I'm trying to work out how much money I'm owed back as a rebate from a company I do business for.

They give me an anual rebate based on my monthly turnover:

£0-£24,999k = 1%
£25k - £49,999 = 2%
£50k - £74,999 = 3%
£75k - £99,999 = 4%
£100k+ = 5%

The rebate is worked out in stages, so if i make £48k one month, I get 2% on £23,001 and 1% on the £24,999.
As I get the rebate annually, i wanted to make a spreadsheet with a column for every months turnover, a total turnover and a rebate due column. Is it possible to make a formula to show the total rebate due, based on all the above?

Its been confusing me all day.

Rafter

Claire, Not easy to explain but if you send me a private message with your e-mail address I can send something over.....

Basically you want it to look like this:

A B
1 Month: Jan

2 Turnover 48,000

3 24999 =if(b2<a3,b2,a3)

4 49999 =if(b2<a4,b2-b3,a4-a3)

5 74999 =if(b2<a5,b2-b3-b4,a5-a4)

6 99999 =if(b2<a6,b2-bc-b4-b5,a6-a5)

7 =if(b2>99999,b2-99999,0)

8

9 1% =a9*b3

10 2% =a10*b4

11 3% =a11*b5

12 4% =a12*b6

13 5% =a13*b7

14 Total =sum(b9:b13)


Good luck

Cmdr_Bond

Rafter wrote: »

Claire, Not easy to explain but if you send me a private message with your e-mail address I can send something over.....

Basically you want it to look like this:

A B
1 Month: Jan

2 Turnover 48,000

3 24999 =if(b2<a3,b2,a3)

4 49999 =if(b2<a4,b2-b3,a4-a3)

5 74999 =if(b2<a5,b2-b3-b4,a5-a4)

6 99999 =if(b2<a6,b2-bc-b4-b5,a6-a5)

7 =if(b2>99999,b2-99999,0)

8

9 1% =a9*b3

10 2% =a10*b4

11 3% =a11*b5

12 4% =a12*b6

13 5% =a13*b7

14 Total =sum(b9:b13)


Good luck

I think some of your cell references are wrong,

And this is how I think it should be

If you use the same layout in Excel, then All you will have to do is put your first months turnover in D2, then copy/paste across. If you require, I can send you the XLS.