+ Reply to Thread
Results 1 to 6 of 6

Progressive Calculation

  1. #1
    nospaminlich
    Guest

    Progressive Calculation

    I'm trying to come up with a formula that will calculate a total from a table
    so if you had a figure of 9600 and you compare it against the table
    4000 0%
    2000 10%
    5000 20%
    5000 30%
    10000 40%
    it would return the answer 720 (First 4000*0%)+(Next 2000*10%)+(Remaining
    3600*20%)

    I found an excellent piece on Progressive Pricing on Chip Pearson's site but
    despite a lot of tinkering I haven't been able to make it work for what I'm
    trying to do above.

    I'd be grateful for any ideas on how to do this.

    Thanks a lot

  2. #2
    Rowan Drummond
    Guest

    Re: Progressive Calculation

    I think there is an error in the example you have given based on the
    table and logic you have described. 9600 should return an answer of 920:

    4000* 0% = 0
    2000*10% = 200
    3600*20% = 720
    Total = 920

    You have also not said what you want to do with numbers which are do not
    fit into your table i.e anything over 26000 so I have let these error
    out. With your table in cells A2:B6 and the value to check in A8 try the
    formula:

    =IF(A8<=A2,A8*B2,
    IF(A8<=SUM(A2:A3),A2*B2+(A8-A2)*B3,
    IF(A8<=SUM(A2:A4),A2*B2+A3*B3+(A8-SUM(A2:A3))*B4,
    IF(A8<=SUM(A2:A5),A2*B2+A3*B3+A4*B4+(A8-SUM(A2:A4))*B5,
    IF(A8<=SUM(A2:A6),A2*B2+A3*B3+A4*B4+A5*B5+(A8-SUM(A2:A5))*B6,
    "Number Too Large")))))

    Hope this helps
    Rowan

    nospaminlich wrote:
    > I'm trying to come up with a formula that will calculate a total from a table
    > so if you had a figure of 9600 and you compare it against the table
    > 4000 0%
    > 2000 10%
    > 5000 20%
    > 5000 30%
    > 10000 40%
    > it would return the answer 720 (First 4000*0%)+(Next 2000*10%)+(Remaining
    > 3600*20%)
    >
    > I found an excellent piece on Progressive Pricing on Chip Pearson's site but
    > despite a lot of tinkering I haven't been able to make it work for what I'm
    > trying to do above.
    >
    > I'd be grateful for any ideas on how to do this.
    >
    > Thanks a lot


  3. #3
    JMB
    Guest

    RE: Progressive Calculation

    Assuming your table is in cells A1:B5 and the value you are analyzing is in
    cell B9, you could try:

    =INDEX(A1:A5*B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1))+((B9-INDEX(SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)))*INDEX(B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)+1))

    confirmed with Control+Shift+Enter after you type (or paste) it in.

    Change cell references as needed.




    "nospaminlich" wrote:

    > I'm trying to come up with a formula that will calculate a total from a table
    > so if you had a figure of 9600 and you compare it against the table
    > 4000 0%
    > 2000 10%
    > 5000 20%
    > 5000 30%
    > 10000 40%
    > it would return the answer 720 (First 4000*0%)+(Next 2000*10%)+(Remaining
    > 3600*20%)
    >
    > I found an excellent piece on Progressive Pricing on Chip Pearson's site but
    > despite a lot of tinkering I haven't been able to make it work for what I'm
    > trying to do above.
    >
    > I'd be grateful for any ideas on how to do this.
    >
    > Thanks a lot


  4. #4
    JMB
    Guest

    RE: Progressive Calculation

    Please disregard - there's an error in the formula.

    "JMB" wrote:

    > Assuming your table is in cells A1:B5 and the value you are analyzing is in
    > cell B9, you could try:
    >
    > =INDEX(A1:A5*B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1))+((B9-INDEX(SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)))*INDEX(B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)+1))
    >
    > confirmed with Control+Shift+Enter after you type (or paste) it in.
    >
    > Change cell references as needed.
    >
    >
    >
    >
    > "nospaminlich" wrote:
    >
    > > I'm trying to come up with a formula that will calculate a total from a table
    > > so if you had a figure of 9600 and you compare it against the table
    > > 4000 0%
    > > 2000 10%
    > > 5000 20%
    > > 5000 30%
    > > 10000 40%
    > > it would return the answer 720 (First 4000*0%)+(Next 2000*10%)+(Remaining
    > > 3600*20%)
    > >
    > > I found an excellent piece on Progressive Pricing on Chip Pearson's site but
    > > despite a lot of tinkering I haven't been able to make it work for what I'm
    > > trying to do above.
    > >
    > > I'd be grateful for any ideas on how to do this.
    > >
    > > Thanks a lot


  5. #5
    Biff
    Guest

    Re: Progressive Calculation

    Hi!

    I get 920....

    Try this:

    =SUMPRODUCT(--(A1>{4000,6000,11000,16000}),(A1-{4000,6000,11000,16000}),{0.1,0.1,0.1,0.1})

    Easier and more flexible if you setup a table. See this for examples:

    http://mcgimpsey.com/excel/variablerate.html

    Biff

    "nospaminlich" <[email protected]> wrote in message
    news:[email protected]...
    > I'm trying to come up with a formula that will calculate a total from a
    > table
    > so if you had a figure of 9600 and you compare it against the table
    > 4000 0%
    > 2000 10%
    > 5000 20%
    > 5000 30%
    > 10000 40%
    > it would return the answer 720 (First 4000*0%)+(Next 2000*10%)+(Remaining
    > 3600*20%)
    >
    > I found an excellent piece on Progressive Pricing on Chip Pearson's site
    > but
    > despite a lot of tinkering I haven't been able to make it work for what
    > I'm
    > trying to do above.
    >
    > I'd be grateful for any ideas on how to do this.
    >
    > Thanks a lot




  6. #6
    Roger Govier
    Guest

    Re: Progressive Calculation

    Hi

    One way
    =MAX(0,A1-4000)*10%+MAX(0,A1-6000)*10%+MAX(0,A1-11000)*10%+MAX(0,A1-16000)*10%-MAX(0,A1-116000)*40%

    This formula takes cumulative 10%'s on each block of values up to 40%, but
    as there is a cap (I assume from the table you posted) after 116,000 any
    value above 116,000 has the cumulative percentage deducted.

    Regards

    Roger Govier


    nospaminlich wrote:
    > I'm trying to come up with a formula that will calculate a total from a table
    > so if you had a figure of 9600 and you compare it against the table
    > 4000 0%
    > 2000 10%
    > 5000 20%
    > 5000 30%
    > 10000 40%
    > it would return the answer 720 (First 4000*0%)+(Next 2000*10%)+(Remaining
    > 3600*20%)
    >
    > I found an excellent piece on Progressive Pricing on Chip Pearson's site but
    > despite a lot of tinkering I haven't been able to make it work for what I'm
    > trying to do above.
    >
    > I'd be grateful for any ideas on how to do this.
    >
    > Thanks a lot


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1