Progressive pricing

[Abbeypost]

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

[Jackson]

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
"Abbeypost" <Abbeypos...@excelbanter.com> wrote in message
news:Abbeypos...@excelbanter.com...

[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" <Abbeypos...@excelbanter.com>
wrote in message
news:Abbeypos...@excelbanter.com...
>

[ar...@payroll.nyc.gov]

On Jan 30, 7:15 am, Abbeypost <Abbeypost.96c5...@excelbanter.com>
wrote:

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

'Jim Cone[_2_ Wrote:
> ;1272174']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" <Abbeypos...@excelbanter.com>
> wrote in message

> news:Abbeypos...@excelbanter.com...-

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




--
Abbeypost

[Vacuum Sealed]

On 1/02/2012 2:56 AM, ar...@payroll.nyc.gov 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.

[Vacuum Sealed]

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.