Abbeypost wrote:
I run a copy shop and want to calculate the total price based on a scale
as follows:

1 - 100 20p
101 - 250 15p
251 - 500 10p
500+ 8p

If the total copies are 400, the total price should be:

100 @ 20p = £20
150 @ 15p = £22.50
150 @ 10p = £15.00

Total price = £57.50

If the total copies are entered in H5 and the total to be paid is shown
in H14, please give the formula and the cell into which it must go.

I have tried to follow other peoples explanations without success,
that's why I have included the information above.

Thanks in anticipation of saving my sanity


Looks to me that the total for 400 copies should be £40.00.

You have misunderstood. We want to smooth the prices out to maintain our margin because we are starting from a low base figure. We don't quote a price for each quantity band because in the example it would average 10p whereas it should be 14.4p. As the quantity goes up so the cost per copy reduces. This is not a con, it's a way of keeping our margin viable. We can then change the figures and do that same thing for black copies.