# Help with Formula

#### visionate

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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### visionate

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

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

#### AliGW

##### Banned
Thanks for letting us know!

Replies
1
Views
466
Replies
10
Views
301
Replies
10
Views
439
Replies
1
Views
163
Replies
3
Views
206

1,195,702
Messages
6,011,200
Members
441,594
Latest member
AVO

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back