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
    #1
    1. Advertisements

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

    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, Mar 28, 2012
    #2
    1. Advertisements

  3. 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))
    >
    > 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, Mar 28, 2012
    #3

  4. >
    > 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
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patricia

    Need formula for progressive dates within a worksheet

    Patricia, Oct 13, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    1,531
    Bernie Deitrick
    Oct 14, 2003
  2. kuhasu

    Progressive Rate in Excel

    kuhasu, May 18, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    272
    Gord Dibben
    May 18, 2004
  3. Guest

    Progressive summing

    Guest, Apr 26, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    188
    Guest
    Apr 27, 2005
  4. Cimjet

    Re: Progressive pricing

    Cimjet, Jan 30, 2012, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    320
    Cimjet
    Jan 30, 2012
  5. Jim Cone

    Re: Progressive pricing

    Jim Cone, Jan 30, 2012, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    318
    Jim Cone
    Jan 30, 2012
Loading...

Share This Page