# Quantity Pricing Question

#### NewGuy123

##### New Member
I have a schedule that lists on the top columns of months and another row that has the number of widgets sold for each month. I'm trying to calculate revenue given different price points depending on how many widgets are sold.

My examplie is that I have Widget pricing of \$2 for first 5, \$1.50 for second 5 purchased, and \$1 for the last 3 purchased. I'm having difficulty creating a formula that is able to account for the different pricing. I want a formula so I can then do an analysis for different scenarios. I will input exactly how many widgets are shipped in each of the months and want the schedule to automatically calculate revenue based on my price points. What creates a big problem is that if there is a month that may have a shipment of widgets that has more than one pricing.

Within the spreadsheet, I have the following pricing setup for this example:
Widget Pricing - 1st Tier / \$2.00 / 5
Widget Pricing - 2nd Tier / \$1.50 / 5
Widget Prcing - 3rd Tier / \$1.00 / 3

Using my example, customer will buy 13 widgets in total.

In Month 1, 4 widgets are at the pricing setup in 1st tier cell (\$2.00).

In Month 2, 5 widgets with revenue calculated as 1 widget multiplied by the 1st tier cell (\$2) and 4 widgets multiplied by the 2nd tier price (\$1.50).

In Month 3, 3 widgets with revenue calculated as 1 widget multiplied by the 2nd tier cel (\$1.50) and 2 widgets multiplied by the 3rd tier cell (\$1.00).

In Month 4, 1 widget will be sold and revenue calculated at 1 widget multiplied by the 3rd tier cell (\$1.00).

Can any one help?

I've considered using a cumulative shipment row for each of the months to help with a calculation but unsure if this will help.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
NewGuy123

Welcome to the MrExcel board!

Is this something along the correct lines? If not, please advise further details. Also consider that small samples of a sheet can be shown on the board (like I have below) using Colo’s HTML Maker:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
or Excel jeanie:
http://www.excel-jeanie-html.de/index.php?f=1

Formulas in N2 and O2 are just copied down.

Excel Workbook
ABCDEFGHIJKLMNO
1JanFebMarAprMayJunJulAugSepOctNovDecNo SoldRevenue
2Widgets Sold45311320.50
3Widgets Sold224.00
4Widgets Sold111311814.50
5Widgets Sold2020206067.50
6
7Pricing Table
8UnitsBase AmountExtra Per Unit
902.00
105101.50
111017.51.00
12
Widgets

Trying to Digest

Peter,

Thanks for your help. I'm still trying to digest/understand your recommendation given that I'm not an expert on vlookups.

One thing I forgot to add was that I was trying to also come up with monthly revenues in the monthly columns which would mean another row that calculated this.

Your Row 2 was exactly what my example is. (I'm assuming that your other rows below row 2 (excluding the pricing table) doesn't apply to your answer.)

And, from what I can tell, your pricing table is correct from the standpoint that 1-5 widgets = \$2/per, 6-10 widgets =\$1.50/per, and 11-13 widgets =\$1.00/per.

Anything that you can help me understand would be great.

Thanks.

Peter,

Thanks for your help. I'm still trying to digest/understand your recommendation given that I'm not an expert on vlookups.

One thing I forgot to add was that I was trying to also come up with monthly revenues in the monthly columns which would mean another row that calculated this.

Your Row 2 was exactly what my example is. (I'm assuming that your other rows below row 2 (excluding the pricing table) doesn't apply to your answer.)

And, from what I can tell, your pricing table is correct from the standpoint that 1-5 widgets = \$2/per, 6-10 widgets =\$1.50/per, and 11-13 widgets =\$1.00/per.

Anything that you can help me understand would be great.

Thanks.
Well, I'm just guessing at exactly what you want because I can't see your sheet and you haven't shown it or described in detail the layout and requirements. If you only have one row of data, then maybe something like this?

B4 and B5 formulas copied across to the right.

Excel Workbook
ABCDEFGHIJKLMNO
1JanFebMarAprMayJunJulAugSepOctNovDecNo SoldRevenue
2Widgets Sold45311320.50
3
4Cumulative Total491213
5Monthly Revenue883.51
6
7Pricing Table
8UnitsBase AmountExtra Per Unit
902.00
105101.50
111017.51.00
12
Widgets

Starting to Understand

Peter,

I'm starting to understand further. Is there anyway to eliminate from the pricing table column B (Base Amt) since it may be difficult to explain to non-Excel experts? I see that it assists with the total revenue calc. since it looks at the total shipments to determine the total revenue. Knowing the total shipments will determine what the total revenue will be based on your column B in the pricing table area.

I was hoping to have a monthly revenue calculations based on widgets sold in row 2 as you have. This monthly revenue calc would be specific to a customer with the uniques price points as I have explained (i.e. 1-5=\$2, 6-10=\$1.50, 11+=\$1.00).

Thanks.

Re: Starting to Understand

Is there anyway to eliminate from the pricing table column B (Base Amt) since it may be difficult to explain to non-Excel experts?
The easiest way would be to move the pricing table to the right of the other data, leave the "Base Amt" in the table, but hide that column (select any cell in the "Base Amt" column then Format|Column|Hide). That way the formulas can still use the base Amount but the user does not see it in the table.

I was hoping to have a monthly revenue calculations based on widgets sold in row 2 as you have. This monthly revenue calc would be specific to a customer with the uniques price points as I have explained (i.e. 1-5=\$2, 6-10=\$1.50, 11+=\$1.00).
What would the layout look like? So far, I have no "Customers" in my sheet. How would it be arranged?

Have you investigated Colo's HTML maker or Excel jeanie yet? If you could show me what the sheet is to look like it would make things a lot easier!

Peter,

Your last spreadsheet's results were exactly what I was looking for. I'm still trying to understand the formulas.

If I have further questions, I wanted to to add to this topic but it wouldn't be until tomorrow sometime. If you're available, I was hopeful you can try to help me out further.

If not, thanks for all of your help.

By the way, I was trying to replace the numbers you had in the pricing table to be consistent with the unit figures that I had but the calcs didn't work. I tried to replace your "0" with "1", your "5" with "6", and your "10" with "11". I wanted these replacements to illustrate that the first unit pricing ("1") would be \$2, sixth purchased unit ("6") would be \$1.50, etc.

Thanks.

By the way, I was trying to replace the numbers you had in the pricing table to be consistent with the unit figures that I had but the calcs didn't work. I tried to replace your "0" with "1", your "5" with "6", and your "10" with "11". I wanted these replacements to illustrate that the first unit pricing ("1") would be \$2, sixth purchased unit ("6") would be \$1.50, etc.
Once again, I would suggest fixing this by hiding columns. I have moved the existing table over to columns R:T and added some extra descriptive info in column Q. To show you what I have done, I have left all columns visible, but you could hide columns R and S.

Excel Workbook
ABCDEFGHIJKLMNOPQRST
1JanFebMarAprMayJunJulAugSepOctNovDecNo SoldRevenuePricing Table
2Widgets Sold45311320.50UnitsUnitsBase Amount\$ Per Unit
31 to 502.00
4Cumulative Total4912136 to 105101.50
5Monthly Revenue883.51> 101017.51.00
Widgets

Replies
1
Views
149
Replies
4
Views
116
Replies
9
Views
402
Replies
3
Views
237
Replies
4
Views
446

1,221,524
Messages
6,160,324
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back