Tricky 'Find Maximum' problem seeks formula

[tx12345]

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

[Ron Coderre]

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
>
>

[Biff]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ron Coderre]

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
> >
> >

[tx12345]

Ron,

Perfect. When I am done with my program, I'll PM you a link for a free download.

Thx again

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>

[Biff]

Hmmm......

Well, I obviously don't understand the problem!

Biff

"tx12345" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>