Question about MAX date

[shdawson]

Hi,


What function would be the best to use for evaluating a range of rows and pick the MAX date from a particular column? In other words, the newest date?


Have some data that has 3 columns:
project id
report date
status indicator.


...with LOTS of rows.


This is output from a repository where people input the status of a task. That status changes now and again. The output I have contains all of the rows (entries) into this repository, by project id.

Need to look at the project date, figure out the newest date, then grab the status id.


Thanks...

 

[mikerickson]

Have you tried MAX ?

Or are your dates really text strings?

 

[shdawson]

the date is like....12/24/2010


So, I guess it is the later.


?


S

 

[shdawson]

Thoughts on Formula:

=SUMPRODUCT(--('Status'!A$2:A$10000=C2),--MAX('Status'!A$2:A$10000),--GRAB('Status'!D$2:D$10000))

Not sure how to handle the MAX part.

GRAB is my psudeo-code to say...."get the value in the D column".


?


Thanks,
S

 

[mikerickson]

GRAB is not an Excel function, but it sounds like VLOOKUP is what you are looking for. Perhaps

=VLOOKUP(MAX(A:A), ,A1:G10000, 7, FALSE)

 

[shdawson]

Yeah, GRAB is pseudo-code.

Your example does not account for consiering the project id value. Need to:

1) match project id
2) get the max date
3) grab the value in the statu id column


?

 

[shdawson]

would this be a SUMPRODUCT thing?

 

[jeffreybrown]

SHD,

Can you post a small representative screen shot of your data with expected results?

http://www.excel-jeanie-html.de/index.php?f=1

 

[shdawson]

Jeffrey,


Hi. Thanks for the post.

This morning, I had to dig deeper and find an answer. So, a light came on to reference a previous posting. So, this posting has segued to...
http://www.mrexcel.com/forum/showthread.php?p=2563479#post2563479


Please let me know if this answers your questions of me now.


Thanks Bro,
S