Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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("A 1:A"&ROW(A1:A5))),1))+((B9-INDEX(SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),MATC H(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)))* INDEX(B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&RO W(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
|
|||
|
|||
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("A 1:A"&ROW(A1:A5))),1))+((B9-INDEX(SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),MATC H(B9,SUBTOTAL(9,INDIRECT("A1:A"&ROW(A1:A5))),1)))* INDEX(B1:B5,MATCH(B9,SUBTOTAL(9,INDIRECT("A1:A"&RO W(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
|
|||
|
|||
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" wrote in message ... 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
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Equity buildup calculation | Excel Worksheet Functions | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
time-clock calculation | Excel Worksheet Functions |