Tiered Pricing Calculator

Hi
this technique is described at http://www.mcgimpsey.com/excel/variablerate1.html

Deleting since I have an update on the next post. Sorry!

I think I'm getting there.. but I'm still coming up short. I changed the last section to just the dollar amounts:

{15;10;6;4} and the number is almost correct, but it's over by about $30k. The marginal formula is going to charge the customer $15 for the entire qty, so let's say it's a quantity of 6k, all 6k will have $15 per item and then 1k-5k will be $10 per item, etc. I only need the first 1k items to be $15 each. I'm not sure this is the right formula?

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

	A	B	C	D
1	Qty	Price	Delta
2	0	$ 15.00	$ 15.00	C2: =B2 -N(B1)
3	1,000	$ 10.00	$ (5.00)
4	5,000	$ 6.00	$ (4.00)
5	10,000	$ 5.00	$ (1.00)
6	20,000	$ 4.50	$ (0.50)
7	50,000	$ 4.00	$ (0.50)
8	100,000	$ 3.50	$ (0.50)
9
10	Qty	Price	Avg Price
11	100	$ 1,500	$ 15.00	B11: =SUMPRODUCT((A11 > $A$2:$A$8) * (A11 - $A$2:$A$8) * $C$2:$C$8)
12	200	$ 3,000	$ 15.00
13	500	$ 7,500	$ 15.00
14	1,000	$ 15,000	$ 15.00
15	2,000	$ 25,000	$ 12.50
16	5,000	$ 55,000	$ 11.00
17	10,000	$ 85,000	$ 8.50
18	20,000	$ 135,000	$ 6.75
19	50,000	$ 270,000	$ 5.40
20	100,000	$ 470,000	$ 4.70
21	200,000	$ 820,000	$ 4.10
22	500,000	$ 1,870,000	$ 3.74

Since I don't know what Excel 10 is, I'll give you a method that will work with Excel as far back as I can remember.

Look at VLOOKUP with the TRUE option. The value you are looking up ON should be sorted lowest to highest. The way VLOOKUP works with the True option is that if it finds an exact match, it uses the value in whatever column you selected. If it does not match it "falls up" to the next lowest value in the searched column and returns the value of the column you want to return.

VLOOKUP is a powerful tool when working with stepped functions.

@mvirtue

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread