Help with Formula

[visionate]

Hi All,

Yesterday I posted a thread for a formula to produce the results of this table, and AliGW helped me out with the formula below:

£5000 - £10,000 - times by 15%
£10,001 - £20,000 - times by 25%
£20,001 - £30,000 - times by 30%
£30,001 + - times by 35%

=AF3*LOOKUP(AF3,{0,5000,10001,20001,30001},{0,0.15,0.25,0.3,0.35})[TR=px"class: cms_table"
[TABLE="class: cms_table, align: left"]
<tbody></tbody>[/TABLE]

However, I just found out that I actually need to formula to calculate the table like this:

For the first £0 - £5,000 - times by 0%
For the next £5000 - £10,000 - times that by 15%
For the next £10,001 - £20,000 - times that by 25%
For the next £20,001 - £30,000 - times that by 30%
for everything above £30,001 + - times that by 35%


So for example, if the value is £45,500
0 (£5,000 x 0%)
+ 750 (£5000 x 15%)
+ 2500 (£10000 x 25%)
+ 3000 (£10000 x 30%)
+ 4650 (£15500 x 35%)
----------------------------
= 10,900

or if the value is £27,250
0 (£5,000 x 0%)
+ 750 (£5000 x 15%)
+ 2500 (£10000 x 25%)
+ 2175 (£7250 x 30%)
----------------------------
= 5425

Cheers

 

[AliGW]

This came up on another forum this morning and this useful link was provided: Excel Income Tax Formula for calculating tax in single cell

 

[visionate]

I am not getting the right figures.

Here is my table

For Excel Formula
5000
10000	0.15
20000	0.10
30000	0.50
	0.50

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Here is my formula.

=SUMPRODUCT(--(AL117>(AM121:AM124)), (AL117-(AM121:AM124)), (AN122:AN125))

But for a value of 45,500 I am getting 30,125

 

[visionate]

Ok I just realised I made the mistake where I had put 0.5 instead of 0.05, thanks it all works now

 

[AliGW]

Thanks for letting us know!