PC Review


Reply
Thread Tools Rate Thread

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
 
Reply With Quote
 
 
 
 
Vacuum Sealed
Guest
Posts: n/a
 
      28th Mar 2012
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))

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.


 
Reply With Quote
 
 
 
 
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))
>
> 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.
 
Reply With Quote
 
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Progressive pricing Jim Cone Microsoft Excel Worksheet Functions 0 30th Jan 2012 06:27 PM
Re: Progressive pricing Cimjet Microsoft Excel Worksheet Functions 0 30th Jan 2012 04:59 PM
Progressive slowing - a chronic condition Steve Windows XP General 2 16th Aug 2004 10:13 AM
Progressive slowing - a chronic condition Steve Windows XP Basics 3 12th Aug 2004 12:32 PM
JPG file progressive loading in internet explorer 6 Shane Ward Windows XP Internet Explorer 1 16th Sep 2003 10:20 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:24 AM.