Finding Dates for Min & Max

  • Thread starter Thread starter JohnI in Brisbane
  • Start date Start date
J

JohnI in Brisbane

Hello there,


I have a worksheet with Stock (or Shares as we call them in Australia)
information, as follows-

Share 21-Aug-03 22-Aug-03 25-Aug-03 26-Aug-03 27-Aug-03 28-Aug-03
29-Aug-03 01-Sep-03 02-Sep-03 03-Sep-03 04-Sep-03 05-Sep-03
NCP 12.47 12.56 12.58 12.58 12.83 13.01 13.1 13.38 13.6 13.72 13.79
13.54

: : :
V V V



The eample above is NewsCorp with the months in row 1, and down the page I
have the prices for each Stock in rows.

Finding the MIN & MAX for each row is easy.

How do I find the most recent dates for the MIN and MAX?

regards and TIA (thanks in advance),

JohnI
 
John,

Not sure where on the sheet you want to enter the formula but here is one way.

With the first quote in C2 (first date in C1) enter in A2:

=INDEX(C:N,1,MATCH(MAX(C2:N2),C2:N2,0))

You will have to adjust the references to suit, but this works on your example
data, and you'll get the idea.

Note: If two or more quotes are the same, the earliest is returned.

HTH
Anders Silvén
 
Anders,

With the data the way I originally had it, the formula you gave me returned
the oldest date with the MAX Price.

I reversed my data by sorting, so the most recent date was in column B etc,
& your formula worked exactly the way I wanted.

I had previously found a much more complicated way to get the result I was
after. Your formula is much much quicker.

Thanks,

JohnI
 
JohnI

I was so preoccupied with finding the basic formula that I didn't re-read your
message before answering. Not only did you ask for the most *recent* MAX date, I
also missed the MIN value.

A good challenge is to find the MAX/MIN dates without sorting. Hmmm... I was
just closing down for tonight.

Best regards,
Anders Silvén
 
Back
Top