Best functions/formulas to use for a sales quotation?
 

Hi

If I sell a product and the price band is as follows:

First 50 sales = £100 each (£5,000)
Next 50 sales = £50 each (£2,500)
Further sales (70) = £25 each (£1,750)

Therefore 170 sales = £9,250 income

I would like to be able to easily adjust sales figures and the price band. For example alter it as follows:

First 30 sales = £70 each (£2,100)
Next 20 sales = £60 each (£1,200)
Further sales (120)= £40 each (£4,800)

Therefore 170 sales = £8,100

My query:

I would like to be able to toggle the number of sales and and amount charged for each banding.

I would also like to be able to input the total number of sales I am being asked to quote. For example increase sales quote from 170 to 230.

The various totals would change automatically based on the pricing bands I input.

Hope this makes sense? If someone can point me in the right direction I'll be able to figure it? Appreciated any advice.

Many thanks
Colin

To clarify, I have attached part of spreadsheet, based on the pricing bands, how do I input an amount of sales in the highlighted cell (B6) and automatically calculate an amount to charge in the cell below (B7)?

Hope you can help.
Thanks

PS Don't seem to be able to upload excel files so hope this notepad file will suffice?