Tricky 'Find Maximum' problem seeks formula

T

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
 
B

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
 
R

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
 
R

Ron Coderre

corrected text:
Using your data in A1:C5

(not A1:B5)


***********
Regards,
Ron
 
T

tx12345

Ron,

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

Thx agai
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating an average of data between two dates 1
Help on multiple criteria for formula and return a date 1
create manufacturing demand based on customer delivery 0
Display row of the maximum in a list 2
How to select one record of each from a column 1
Numbering years and months 5
Can't solve simple? problem. Can some genius out there help! 2
Finding the weighted average of text that have been given values 1

Top