Finding Dates for Min & Max

  • Thread starter JohnI in Brisbane
  • 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
 
A

Anders S

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
 
J

JohnI in Brisbane

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
 
A

Anders S

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
 
Top