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

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.

That's a terrible way to calculate discount.
You tell people if you buy 400, its 10p then it should be 40.00 not the way you
calculate.
You wouldn't see me twice.
Cimjet
"Abbeypost" wrote in message
...

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




--
Abbeypost

I had a formula ready to go and then noticed that you posted from excelbanter.com.
Does anybody there ever acknowledge a response to a question?
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Special Print XL add-in: rows to repeat a bottom)




"Abbeypost"
wrote in message
...

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
--
Abbeypost

On Jan 30, 7:15*am, 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

--
Abbeypost

H14=(0.2*H5)-
(0.05*(MAX(H5-100,0)))-0.05*(MAX(H5-250,0))-0.02*(MAX(H5-500,0))

Allan Rogg

I know it sound pathetic, but I forgot where I had asked the question, hence the lack of a reply. The point of this is to maintain the margin and smooth out the price. We don't want to say X are at one price or Y are at another, rather the price for X is ***. This is driving me nuts.

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.

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.

On 1/02/2012 2:56 AM, wrote:
=(0.2*H5)-
(0.05*(MAX(H5-100,0)))-0.05*(MAX(H5-250,0))-0.02*(MAX(H5-500,0))

Just to expand on Allan's formula, I converted the actual decimals to a
cell reference to allow for price changes.

Keep in mind though this is only handy for an on-the-spot pricing and
should not be used in a Historical/Archival way as it will have a global
effect, changing all cells that rely on this.

=(H1*H5)-(H2*(MAX(H5-100,0)))-H2*(MAX(H5-250,0))-H3*(MAX(H5-500,0))

Change the cells addresses to suit your sheet layout/structure, you
could even house the pricing on another sheet and point to those cells.

=(PricingSheet!H1*H5)-(PricingSheet!H2*(MAX(H5-100,0)))-PricingSheet!H2*(MAX(H5-250,0))-PricingSheet!H3*(MAX(H5-500,0))

HTH
Mick.

On 29/03/2012 9:54 AM, Vacuum Sealed wrote:
On 1/02/2012 2:56 AM, wrote:
=(0.2*H5)-
(0.05*(MAX(H5-100,0)))-0.05*(MAX(H5-250,0))-0.02*(MAX(H5-500,0))

Just to expand on Allan's formula, I converted the actual decimals to a
cell reference to allow for price changes.

Keep in mind though this is only handy for an on-the-spot pricing and
should not be used in a Historical/Archival way as it will have a global
effect, changing all cells that rely on this.

=(H1*H5)-(H2*(MAX(H5-100,0)))-H2*(MAX(H5-250,0))-H3*(MAX(H5-500,0))

Change the cells addresses to suit your sheet layout/structure, you
could even house the pricing on another sheet and point to those cells.

=(PricingSheet!H1*H5)-(PricingSheet!H2*(MAX(H5-100,0)))-PricingSheet!H2*(MAX(H5-250,0))-PricingSheet!H3*(MAX(H5-500,0))


HTH
Mick.


Oops.

need to explain the structure of the pricing layout.

Pseudo Cell locations:

H1 = .20
H2 = .05
H3 = .05

Cheers
Mick.

H14=(0.2*H5)-
(0.05*(MAX(H5-100,0)))-0.05*(MAX(H5-250,0))-0.02*(MAX(H5-500,0))

Allan Rogg

Hi Allan

Don't mean to second guess your formula, but something was bothering me
with it.

If you change the price in the 3rd Tier pricing range 251 - 500 to say
0.08, it does not alter the overall sum. I spent a bit of time breaking
it down to each level and came up with the following:

D1 = 0.20
D2 = 0.15
D3 = 0.10

With $H$5 = 400

=IF($H$5<101,($H$5*D1),($D$1*100)) = 20.00
=IF($H$5250,($D$2*150),($D$2*($H$5-100))) = 22.50
=IF($H$5250,(($H$5-250)*$D$3),0) = 15.00

= 57.50

Conversely, if you change the 3rd tier pricing:

D1 = 0.20
D2 = 0.15
D3 = 0.08

Using the same formula's above you get: = 54.50

When this is applied to your current structure it remains unchanged.

I attempted to emulate your formula to take into account the variation
in Price Tier changes but came up zip so I hope this helps with the next
attempt.

Cheers
Mick.

Thanks for the reply which works great. I think that now I see the structure, I can change the figures or alter the quantity/price bands if needed.

Sanity restored THANKS.