Result between two numbers

[Guest]

I need a formula to return a percentage if spend is between two figures. e.g.
if spend is between £10,000 and £20,000 I need the formula to return the
percentage discount for this spend, likewise if it is between £20,000 and
£30,000 the discount will be different.

The discounts are held on the spreadsheet so I just need the formula to link
to this and return the appropriate discount %.

Any ideas?

 

[Bernie Deitrick]

Luke,

Are the rates cumulative, or do they apply just once? That is, if you spend 25,000, do you get one
discount on the first 10,000, another discount on the second 10,000, and a third discount on the
last 5,000, or just one discount on the whole 25,000?

HTH,
Bernie
MS Excel MVP

 

[Guest]

No just one discount on £25,000. And they'd get a different discount on the
whole of £35,000 if they spent that. Therefore if they spent £27,355 they
would get the discount for spending more than £25,000.

So I need a formula that recognised that £27,355 is more than £25,000 but
less than £35,000

 

[Bernie Deitrick]

Luke,

Make up a table like this starting in cell A1:

Amount Discount
0 0
10000 10%
20000 20%
30000 30%
40000 40%

Then use a formula like

=VLOOKUP(D2,$A$1:$B$6,2)

where the amount spent is in cell D2 to return the discount.

To calc the final amount, use

=D2*(1-VLOOKUP(D2,$A$1:$B$6,2))

This will apply a 10% discount to all amounts between 10,000 and 19,999.99.

HTH,
Bernie
MS Excel MVP