Tiered prices

[Guest]

Hi all

Have a real problem here

The Table is such

A
1 =<60 15
2 =<200 1
3 =<400
4 =<600
5 =<800
6 >800

Column A represents screws and column B, cents per screw
What I need to do is calculate an order cost so if someone ordered 450 screws the charge would be $37.50. ( 60 @ 15c, 140 @ 10c, 200@ 8c and 50 @ 6c

How do I do this? and say if a customer says he wants $200 of screws how do I calculate how many he gets

Thank

Alb

 

[JE McGimpsey]

Check out my page on using variable rates with SUMPRODUCT() at

http://www.mcgimpsey.com/excel/taxvariablerate.html


For the case where your customer asks for $200 of screws, an easy way is
to use the formula you develop from the above, and use Goal Seek
(Tools/Goal Seek) to calculate the number of items.