# Working out accruing %'s in excel

#### ClaireCat

##### New Member
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.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Airfix9

##### Well-known Member
Edit this to suit your workbook and cell references:

=IF(A1>=100000,((A1-100000)*0.05+2500),IF(A1>=75000,((A1-75000)*0.04+1500),IF(A1>=50000,((A1-50000)*0.03+750),IF(A1>=25000,((A1-25000)*0.02+250),A1*0.01))))

#### shg

##### MrExcel MVP
Another way:

Code:
``````      ----A----- ---B---- C ----D----- ---E----
1    Turnover  Schedule    Turnover   Rebate
2                          \$ 25,000    \$ 250
3         \$ 0        1%    \$ 26,000    \$ 270
4    \$ 25,000        2%    \$ 50,000    \$ 750
5    \$ 50,000        3%    \$ 51,000    \$ 780
6    \$ 75,000        4%    \$ 75,000  \$ 1,500
7   \$ 100,000        5%    \$ 76,000  \$ 1,540
8                         \$ 100,000  \$ 2,500
9                         \$ 101,000  \$ 2,550``````

The formula in E2 and down is

=SUMPRODUCT( (D2>\$A\$3:\$A\$7) * (D2-\$A\$3:\$A\$7) * (\$B\$3:\$B\$7-\$B\$2:\$B\$6))

1,109,389
Messages
5,528,419
Members
409,817
Latest member
JiNXX9500

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...