Posted to microsoft.public.excel.worksheet.functions
|
|
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
|