Progressive pricing

[Abbeypost]

I run a copy shop and want to calculate the total price based on a scal
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 show
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 sanit

 

[Jackson]

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.

 

[Cimjet]

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

 

[Jim Cone]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[arogg]

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

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

Allan Rogg

 

[Abbeypost]

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 margi
and smooth out the price. We don't want to say X are at one price or
are at another, rather the price for X is ***. This is driving m
nuts.

;1272174']I had a formula ready to go and then noticed that you poste
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" <[email protected]>
wrote in message

I run a copy shop and want to calculate the total price based on 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 i 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

 

[Vacuum Sealed]

=(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.

 

[Vacuum Sealed]

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.

 

[Vacuum Sealed]

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$5>250,($D$2*150),($D$2*($H$5-100))) = 22.50
=IF($H$5>250,(($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.