Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 362

# 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
0
xllvr
• 6
• 6
• 3
2 Solutions

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

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

Thomas
0

Author Commented:
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

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

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

Author Commented:
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

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

Commented:

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

Author Commented:
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

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

Author Commented:
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

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

Author Commented:
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

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

Commented:
No issue though, vindication is good enough for me.

Thomas
0

Author Commented:
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

• 6
• 6
• 3
Tackle projects and never again get stuck behind a technical roadblock.