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
  • Last Modified:

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
Asked:
xllvr
  • 6
  • 6
  • 3
2 Solutions
 
nutschCommented:
Try this, entered as an array formula (Ctrl+Shift+Enter)

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

Thomas
0
 
xllvrAuthor 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
 
barry houdiniCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
nutschCommented:
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
 
xllvrAuthor 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
 
nutschCommented:
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
 
barry houdiniCommented:
Disregard my comments above.....

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
 
xllvrAuthor 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
 
nutschCommented:
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
 
xllvrAuthor 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
 
nutschCommented:
Isn't 12/1/11 after 3/1/11
0
 
xllvrAuthor 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.

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.
0
 
barry houdiniCommented:
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
 
nutschCommented:
No issue though, vindication is good enough for me.

Thomas
0
 
xllvrAuthor 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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