How to Create a Discount Calculator based on volume

[wilson.robin]

I am trying to create a cost calculator form, which will automatically
calculate the discounted monthly price for a service based on the
number of units. I want to have a single cell which will calculate the
discount rate based on the number of units.

The discounts are:

if units =<50 price = 10
if units =>51 and <101 price = 9.75
if units =>101 and <201 price = 8.75
if units =>201 and <301 price = 8.00
if units =>301 and <401 price = 7.00
if units =>501 and <501 price = 6.00
if units =>501 price = 5.00

I have tried to create a module in VBA, and tried to figure out a
formula that would do this -- but failed. I am neither a programmer
nor an Excel expert, and know when I have ventured too far into terra
incognita.

Anybody know of a solution for this problem?

Thanks

 

[Alan]

With number of units in A1,
=VLOOKUP(A1,{0,10;51,9.75;101,8.75;201,8;301,7;401,6;501,5},2,TRUE)
Regards,
Alan.

 

[Peo Sjoblom]

A formula

=VLOOKUP(B1,{0,0;1,10;51,9.75;101,8.75;201,8;301,7;401,6;501,5},2)

where the quantity is put in B1

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email)