|
Tricky 'Find Maximum' problem seeks formula
Hi I have an Excel brain twister for you. I am tryng to find a simple spreadsheet formula to address the scenario found below. Any ideas will be warmly received. The scenario is this: -|a..... |b.......|c.............| 1 open| close| required 2 12/1 | 12/5 | 1000 3 12/2 | 12/2 | 5000 4 12/3 | 12/4 | 2000 5 12/4 | 12/4 | 4000 In this case, I want to track what the maximum requirement is at any given time. As long as the item is open, the requirement is in force. Once it is closed, it is no longer necessary. At the start of 12/1 the maximum requirement was 1000 At the end of 12/1 the maximum requirement was 1000 At the start of 12/2 the maximum requirement was 6000 At end of 12/2 the maximum requirement was 1000 At the start of 12/3 the maximum requirement was 3000 At the end of 12/3 the maximum requirement was 3000 At the start of 12/4 the maximum requirement was 7000 At the end of 12/4 the maximum requirement was 1000 Thus, during this period, the maximum needed was 7000. Is there a simple formula to track this change? Many Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=490867 |
Tricky 'Find Maximum' problem seeks formula
Hi!
This can't be done using worksheet formulas. Any formula would be dynamic in that when an item is closed the formula would return a value based on the current conditions. A formula has no way of storing "historical" data. This would require VBA programming. I can't help with that, sorry! Biff "tx12345" wrote in message ... Hi I have an Excel brain twister for you. I am tryng to find a simple spreadsheet formula to address the scenario found below. Any ideas will be warmly received. The scenario is this: -|a..... |b.......|c.............| 1 open| close| required 2 12/1 | 12/5 | 1000 3 12/2 | 12/2 | 5000 4 12/3 | 12/4 | 2000 5 12/4 | 12/4 | 4000 In this case, I want to track what the maximum requirement is at any given time. As long as the item is open, the requirement is in force. Once it is closed, it is no longer necessary. At the start of 12/1 the maximum requirement was 1000 At the end of 12/1 the maximum requirement was 1000 At the start of 12/2 the maximum requirement was 6000 At end of 12/2 the maximum requirement was 1000 At the start of 12/3 the maximum requirement was 3000 At the end of 12/3 the maximum requirement was 3000 At the start of 12/4 the maximum requirement was 7000 At the end of 12/4 the maximum requirement was 1000 Thus, during this period, the maximum needed was 7000. Is there a simple formula to track this change? Many Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=490867 |
Tricky 'Find Maximum' problem seeks formula
Try this:
Using your data in Cells A1:B5.... D1: (any date you enter) E1: =SUMPRODUCT(+((A2:A5)<=D1)*(D1<=(B2:B5))*C2:C5) That will return the sum of all required amounts for items that are active as of the input date. Does that help? *********** Regards, Ron "tx12345" wrote: Hi I have an Excel brain twister for you. I am tryng to find a simple spreadsheet formula to address the scenario found below. Any ideas will be warmly received. The scenario is this: -|a..... |b.......|c.............| 1 open| close| required 2 12/1 | 12/5 | 1000 3 12/2 | 12/2 | 5000 4 12/3 | 12/4 | 2000 5 12/4 | 12/4 | 4000 In this case, I want to track what the maximum requirement is at any given time. As long as the item is open, the requirement is in force. Once it is closed, it is no longer necessary. At the start of 12/1 the maximum requirement was 1000 At the end of 12/1 the maximum requirement was 1000 At the start of 12/2 the maximum requirement was 6000 At end of 12/2 the maximum requirement was 1000 At the start of 12/3 the maximum requirement was 3000 At the end of 12/3 the maximum requirement was 3000 At the start of 12/4 the maximum requirement was 7000 At the end of 12/4 the maximum requirement was 1000 Thus, during this period, the maximum needed was 7000. Is there a simple formula to track this change? Many Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=490867 |
correction of minor (and obvious) typo
corrected text:
Using your data in A1:C5 (not A1:B5) *********** Regards, Ron "Ron Coderre" wrote: Try this: Using your data in Cells A1:B5.... D1: (any date you enter) E1: =SUMPRODUCT(+((A2:A5)<=D1)*(D1<=(B2:B5))*C2:C5) That will return the sum of all required amounts for items that are active as of the input date. Does that help? *********** Regards, Ron "tx12345" wrote: Hi I have an Excel brain twister for you. I am tryng to find a simple spreadsheet formula to address the scenario found below. Any ideas will be warmly received. The scenario is this: -|a..... |b.......|c.............| 1 open| close| required 2 12/1 | 12/5 | 1000 3 12/2 | 12/2 | 5000 4 12/3 | 12/4 | 2000 5 12/4 | 12/4 | 4000 In this case, I want to track what the maximum requirement is at any given time. As long as the item is open, the requirement is in force. Once it is closed, it is no longer necessary. At the start of 12/1 the maximum requirement was 1000 At the end of 12/1 the maximum requirement was 1000 At the start of 12/2 the maximum requirement was 6000 At end of 12/2 the maximum requirement was 1000 At the start of 12/3 the maximum requirement was 3000 At the end of 12/3 the maximum requirement was 3000 At the start of 12/4 the maximum requirement was 7000 At the end of 12/4 the maximum requirement was 1000 Thus, during this period, the maximum needed was 7000. Is there a simple formula to track this change? Many Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=490867 |
Tricky 'Find Maximum' problem seeks formula
Ron, Perfect. When I am done with my program, I'll PM you a link for a free download. Thx again -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=490867 |
Tricky 'Find Maximum' problem seeks formula
Hi Ron!
Not sure how this is supposed to work, but........ A2 = 12/1 B2 = empty (not closed, still open) C2 = 1000 D1 = 12/4 Formula return = 0 Biff "Ron Coderre" wrote in message ... Try this: Using your data in Cells A1:B5.... D1: (any date you enter) E1: =SUMPRODUCT(+((A2:A5)<=D1)*(D1<=(B2:B5))*C2:C5) That will return the sum of all required amounts for items that are active as of the input date. Does that help? *********** Regards, Ron "tx12345" wrote: Hi I have an Excel brain twister for you. I am tryng to find a simple spreadsheet formula to address the scenario found below. Any ideas will be warmly received. The scenario is this: -|a..... |b.......|c.............| 1 open| close| required 2 12/1 | 12/5 | 1000 3 12/2 | 12/2 | 5000 4 12/3 | 12/4 | 2000 5 12/4 | 12/4 | 4000 In this case, I want to track what the maximum requirement is at any given time. As long as the item is open, the requirement is in force. Once it is closed, it is no longer necessary. At the start of 12/1 the maximum requirement was 1000 At the end of 12/1 the maximum requirement was 1000 At the start of 12/2 the maximum requirement was 6000 At end of 12/2 the maximum requirement was 1000 At the start of 12/3 the maximum requirement was 3000 At the end of 12/3 the maximum requirement was 3000 At the start of 12/4 the maximum requirement was 7000 At the end of 12/4 the maximum requirement was 1000 Thus, during this period, the maximum needed was 7000. Is there a simple formula to track this change? Many Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=490867 |
Tricky 'Find Maximum' problem seeks formula
Hmmm......
Well, I obviously don't understand the problem! Biff "tx12345" wrote in message ... Ron, Perfect. When I am done with my program, I'll PM you a link for a free download. Thx again -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=490867 |
| All times are GMT +1. The time now is 09:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com