Solved

# Finding max date based on conditions

Posted on 2012-08-10
311 Views
Hi all,

I've attached a sample file of what I'm trying to accomplish.  Detailed notes inside the file.  Would like to find and return the last usage date of an item to a new column via a formula.  I have tried a few versions of MAX incorporating IF but have not had luck.

Thank you!
EE-Last-Use-Date.xlsx
0
Question by:xllvr

LVL 39

Expert Comment

Try this, entered as an array formula (Ctrl+Shift+Enter)

=MAX(IF(\$A\$5:\$A\$31=A5,\$F\$5:\$F\$31,0))

Thomas
0

LVL 1

Author Comment

Thanks, Thomas...I already tried that.  It does deliver an answer but it's not the correct answer.  Did you try it in the attachment I sent?  I put the correct answer in there so you could try and match it.

What that formula does is return the last date even if the usage was zero.  I want it to return the last date that is greater than zero.  It will make sense in the attachment.

Any other suggestions?  Thanks!
0

LVL 50

Expert Comment

Hello xllvr, Thomas' suggestion works for me - I suspect you didn't "array enter" the formula. Paste it into J5 then select that cell, press F2 to select formula then hold down CTRL and SHIFT keys while pressing ENTER. If done correctly then curly braces like { and } will appear around the formula in the formula bar.

Alternatively this version doesn't need "array entry"

=MAX(INDEX((\$A\$5:\$A\$31=A5)*\$F\$5:\$F\$31,0))

regards, barry
0

LVL 39

Expert Comment

Is that what you need, taking into account the quantity?

=MAX(IF(\$A\$5:\$A\$31=A5,IF(\$G\$5:\$G\$31>0,\$F\$5:\$F\$31,0),0))
0

LVL 1

Author Comment

Actually, I did provide the appropriate Ctrl+Shift+Enter to create the array.  The formula isn't providing the correct answer.

The correct answer would be 3/1/11 for Item #1.  That is the last date the Item shows any quantity (500).  The fields below for Item #1 show zero usage.  Does that seem clear now?

Thanks!
0

LVL 39

Accepted Solution

Actually, you have two entries with quantities on 12/1/2011

=MAX(IF(\$A\$5:\$A\$31=A5,IF(\$G\$5:\$G\$31>0,\$F\$5:\$F\$31,0),0))
0

LVL 50

Assisted Solution

So you mean the last date positionally, not latest date? If so try this formula

=LOOKUP(2,1/(A\$5:A\$31=A5)/(G\$5:G\$31>0),F\$5:F\$31)

regards, barry
0

LVL 1

Author Comment

Huge apologies if I wan't clear about the quantity being an element.  I was so in my head with that detail.  Sorry!

Looks like Barry nailed it.  I just couldn't get the other formulas above to work.  Many thanks for all the input!
0

LVL 39

Expert Comment

So you needed the last line with a quantity, rather than the last date (temporally) with a quantity?

BTW, last formula I provided was an array formula too.

Thomas
0

LVL 1

Author Comment

I needed the last usage date with a quantity for an Item.  For some reason, your last formula still gives me 12/1/11 for an answer.  I did enter it as an array.

I would have thought Barry's wouldn't work based on his comment about it being positional, but it does appear to work.

I appreciate all your help on this!
0

LVL 39

Expert Comment

Isn't 12/1/11 after 3/1/11
0

LVL 1

Author Comment

What a dolt!  I am so sorry, Thomas.  You are absolutely right!  In mocking up the data, I moved a little too quickly without really considering the dates for each revision.

0

LVL 50

Expert Comment

No problem - just click on request attention, you can re-assign the points to Thomas if the mods can re-open the question

regards, barry
0

LVL 39

Expert Comment

No issue though, vindication is good enough for me.

Thomas
0

LVL 1

Author Closing Comment

Thanks for sharing the points, guys.  I appreciate you both being engaged in the process.  Thomas, apologies for not seeing your answer was spot on!
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…