# Re: Progressive pricing

Discussion in 'Microsoft Excel Worksheet Functions' started by arogg@payroll.nyc.gov, Jan 31, 2012.

1. ### Guest

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

, Jan 31, 2012

2. ### Vacuum SealedGuest

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

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, Mar 28, 2012

3. ### Vacuum SealedGuest

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))
>
> 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, Mar 28, 2012
4. ### Vacuum SealedGuest

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

Vacuum Sealed, Mar 29, 2012