Tiered Pricing formula

[egotrich]

I am helping a friend with a business plan for his small bbq business. He currently has a smoker that can hold up to 24 boston butts (roughly 192 lbs of meat). I am hoping to create a easy to use workable excel document where he can simply manipulate 1 value:

1. Number of people needing to be fed

His local competitors do not have a tiered pricing system, they simply use a per person pricing breakdown. If someone tells Sonny's BBQ they are feeding 150 people, sonny's says that roughly 1 pound of meat feeds 5 people. Since Sonny's (here anyway) charges $11.99 per pound, the price is simply $359.70 (or 150/5 = 30 lbs, and 30 X $11.99 = $359.70).... Where as my friend can do it for more like $120!

I'm hoping to create the following tiered pricing system in a formula:
0-8 lbs = $5.63 per lb
9-16 lbs = $5.07 per lb
17-32 lbs = $4 per lb
33-72 lbs = $3.50 per lb
>73 lbs = $2.50 per lb

Any help is appreciated! Thank you.

[oeldere]

you can use vlookup for that kind of job.

[egotrich]

please forgive my ignorance, but what is vlookup?

[oeldere]

Vlookup is the formula to use.

add the excel file on t he forum, without confidential information, and I will add the formula in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[shg]

	A	B	C	D	E
1	Wgt	$/lb	Delta
2	0	$ 5.63	$ 5.63		C2: =B2-N(B1)
3	8	$ 5.07	$ (0.56)
4	16	$ 4.00	$ (1.07)
5	32	$ 3.50	$ (0.50)
6	73	$ 2.50	$ (1.00)
7
8
9	Wgt	Total	$/lb
10	5	$ 28.15	$ 5.63		B10: =SUMPRODUCT((A10 > $A$2:$A$6) * (A10 - $A$2:$A$6) * $C$2:$C$6)
11	8	$ 45.04	$ 5.63		C10: =B10/A10
12	9	$ 50.11	$ 5.57
13	16	$ 85.60	$ 5.35
14	17	$ 89.60	$ 5.27
15	32	$ 149.60	$ 4.68
16	33	$ 153.10	$ 4.64
17	73	$ 293.10	$ 4.02
18	200	$ 610.60	$ 3.05
19	500	$ 1,360.60	$ 2.72
20	1000	$ 2,610.60	$ 2.61

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Aardigspook]

Welcome to the forum.

With lbs in A2, put this in B2:
=LOOKUP(A2,{0,9,17,33,73},A2*{5.63,5.07,4,3.5,2.5})

That will charge $5.63/lb up to 8.9999...lbs, then $5.07/lb from 9lbs to 16.9999...lbs, etc, etc.

Hope that does what you want.

Edit: sorry, you wanted to just put in the number of people, not lbs. Assuming you want the lbs to be people/5, do this:
A2: no of people
B2:

Please Login or Register  to view this content.

[egotrich]

ok I think I uploaded the file properly. Basically I want the user to be able to manipulate the RED value (number of people) in cell C2... and that automatically change C3 to the number of lbs (which is 1 pound per five people... and that formula is already in there) and then B7 reflect the new tiered price... in order to arrive at the final retail price in C7

[Aardigspook]

Put this in B7:

Please Login or Register  to view this content.

[egotrich]

WOW THAT DID IT!!!! YA'LL are awesome! What if I wanted to be able to manipulate the number of pounds instead? see attached....

[egotrich]

Nevermind.... figured it out..... thank you all so much!!!!!

[Aardigspook]

The previous formula was already based on the number of pounds, since you had calculated that from the number of people / 5, so you can use the same formula again - this time in B10, referring to C6:

Please Login or Register  to view this content.

Edit - never mind, you figured it out yourself

Glad we could help and thanks for the rep.

If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.