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.