When was the last time a series was that high

Hi

I have a timeseries (please see the attached file for a homemade example) and I need to find an easy way to calculate whenever the figure was that high. As my time series is updated quarterly with new data I would like it to be done automatically.

In this example the figure was 92.1 in Q4 2012. I can manually see that it was higher in Q1 2002 (92.5). But I would like it to be calculated automatically. ANy suggestions?

So, if you want the previous HIGHEST value, then in cell C52, enter:

Formula:

Please Login or Register  to view this content.

=CHOOSE(CEILING(MONTH(INDEX($A:$A,MATCH(MAX($B:$B),$B:$B,0)))/3,1),"Q1","Q2","Q3","Q4")&TEXT(INDEX($A:$A,MATCH(MAX($B:$B),$B:$B,0),0)," YYYY")
Try this for Time and
=MAX($B:$B) for value

Or, if you want to know the last time the current value was matched / exceeded, then use this ARRAY formula (press Ctrl-Shift-Enter):

Formula:

Please Login or Register  to view this content.

Happy to help. Thanks for the feedback