Excel 2007 : Calculate price using a pricing scale based on cumulative sales

Hello,

Using Excel 2007.

I have a pricing scale based on total cumulative annual sales:

0-1000 units - $3/unit
1001-2000 units - $2/unit
2001- 3000 units - $1/unit
3001 units and over - $0.5/unit

I place 1 order per month, different quantity every time.

What I need to calculate is how much I will be paying per unit at any given month in a year.

For ex:

Cumulative sales in May are 900 units and cumulative sales in June happen to be 1200 units (a purchased of 300 in June), then June unit price is ((100*3)+(200*2))/300. In order to do what-if scenarios I need to find a formula that could do this calculation given the pricing scale.

Does anyone know how this can be done?

Thank you

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Not to sure what you mean, is this any good?

Okay, is this closer?