Link to home
Start Free TrialLog in
Avatar of xllvr
xllvrFlag for United States of America

asked on

Finding max date based on conditions

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
Avatar of nutsch
nutsch
Flag of United States of America image

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

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

Thomas
Avatar of xllvr

ASKER

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!
Avatar of barry houdini
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
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))
Avatar of xllvr

ASKER

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!
ASKER CERTIFIED SOLUTION
Avatar of nutsch
nutsch
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xllvr

ASKER

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!
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
Avatar of xllvr

ASKER

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!
Isn't 12/1/11 after 3/1/11
Avatar of xllvr

ASKER

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.

Is there any way I can assign more points to you for your patience and tenacity, never mind having the correct answer?   Ugh...I feel horrible about this.
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
No issue though, vindication is good enough for me.

Thomas
Avatar of xllvr

ASKER

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!