Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
nospaminlich
 
Posts: n/a
Default 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   Report Post  
Rowan Drummond
 
Posts: n/a
Default 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   Report Post  
JMB
 
Posts: n/a
Default 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   Report Post  
JMB
 
Posts: n/a
Default 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   Report Post  
Biff
 
Posts: n/a
Default 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   Report Post  
Roger Govier
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Equity buildup calculation JimDandy Excel Worksheet Functions 5 September 6th 05 05:57 PM
Calculation Setting in Excel Stuart Bisset Excel Discussion (Misc queries) 0 June 17th 05 09:54 AM
How do I use a rounded calculation result in another calculation? vnsrod2000 Excel Worksheet Functions 1 January 26th 05 11:11 PM
How do I use a rounded calculation result in another calculation? vnsrod2000 Excel Worksheet Functions 1 January 26th 05 10:36 PM
time-clock calculation dokliver Excel Worksheet Functions 3 October 28th 04 09:07 PM


All times are GMT +1. The time now is 01:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"