Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tx12345
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
tx12345
 
Posts: n/a
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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



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
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Date stamp formula results? Tricky problem? mjp Excel Discussion (Misc queries) 0 November 18th 05 05:11 PM
Problem with Formula John Excel Worksheet Functions 15 September 19th 05 12:55 PM
Baffling formula problem Ken Schmidt Excel Discussion (Misc queries) 2 December 21st 04 08:52 AM


All times are GMT +1. The time now is 04:49 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"