# Re: Progressive pricing

arogg@payroll.nyc.gov
Guest
Posts: n/a

 31st Jan 2012
On Jan 30, 7:15*am, Abbeypost <(E-Mail Removed)>
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

Vacuum Sealed
Guest
Posts: n/a

 28th Mar 2012
On 1/02/2012 2:56 AM, http://www.pcreview.co.uk/forums/(E-Mail Removed) 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
Guest
Posts: n/a

 28th Mar 2012
On 29/03/2012 9:54 AM, Vacuum Sealed wrote:
> On 1/02/2012 2:56 AM, (E-Mail Removed) 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
Guest
Posts: n/a

 29th Mar 2012

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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Jim Cone Microsoft Excel Worksheet Functions 0 30th Jan 2012 06:27 PM Cimjet Microsoft Excel Worksheet Functions 0 30th Jan 2012 04:59 PM Steve Windows XP General 2 16th Aug 2004 10:13 AM Steve Windows XP Basics 3 12th Aug 2004 12:32 PM Shane Ward Windows XP Internet Explorer 1 16th Sep 2003 10:20 AM

Features

Newsgroups

All times are GMT +1. The time now is 06:10 AM.