Calculate percentages based on the value of a number

[themorganator4]

Hi,

Is there any way to calculate percentages based on the value of a number input in a cell? for example:

0-£1000 = -10%
£1001-£1500 = -15%
£1501-£2000 = -20%

So if I input a figure in a cell, based on the formula, it will automatically calculate the percentage range for that number (as above) so if I input £450 it will automatically deduct 10%, giving me the answer of 405, if I input £1400 it will deduct 15% and so on..

Thanks.

 

[steve the fish]

Heres a formula that may help:

=A1*(1-LOOKUP(A1,{-1E+100,0,1000,1500,2000}+0.1,{0,10,15,20,0})%)

when your value is typed in A1.

 

[themorganator4]

Thank you, now how would I amend this?

I need to add in more ranges and percentages

 

[steve the fish]

You add bits in between the curly brackets. However it sounds like maybe you would be better creating a look up table rather than hard coding into formula. How many more ranges are you talking about?

 

[themorganator4]

also I made an error in the first post, if the value is £0-£999 i need it to deduct £35 rather than deduct a percentage

 

[steve the fish]

So if its £10 its negative?

 

[themorganator4]

So if its £10 its negative?

yes although for the job in hand, the likelyhood of the figure being below £100 is almost impossible so it wouldn't matter so much

 

[themorganator4]

Ive worked out how to amend the formula as needed except for the £0-999 deducting £35 part

 

[steve the fish]

See if this helps:

=IF(A1<1000,A1-35,A1*(1-LOOKUP(A1,{999.9,1500,2000}+0.1,{15,20,0})%))

 

[themorganator4]

See if this helps:

=IF(A1<1000,A1-35,A1*(1-LOOKUP(A1,{999.9,1500,2000}+0.1,{15,20,0})%))

perfect. Thanks for your help