0

I have a spreadsheet that is made up of 2 columns - Each row contains a date and then a river gauge reading. The data covers 20 years - 1995-2015. I would like a formula that searches for the max river level within a month & year, then returns that corresponding date. To make things easier, I added two additional columns, that break down the date on the left into month and year. In the example below, I would want to know the day of the max reading in January 2015. The answer would be January 29. Keep in mind, I have the gauge reading for every day from Jan 1 1995 to Jan 1 2015.

Column A - Full Date
Column B - Month 
Column C - Year 
Column D - Gauge Reading

January 29, 2015    1   2015    26.86
January 26, 2015    1   2015    26.84
January 27, 2015    1   2015    26.78
January 23, 2015    1   2015    26.7
January 25, 2015    1   2015    26.69
January 15, 2015    1   2015    26.68
January 24, 2015    1   2015    26.65
January 16, 2015    1   2015    14.09
January 17, 2015    1   2015    13.72
Improve this question
3
  • Welcome to Super User. Nicely detailed question. Can you tell us what you have tried so far? Please include any formulas you are working with.
    – CharlieRB
    Feb 6, 2015 at 21:03
  • I would use a pivot table, if I'm not mistaken you have about 7000 records. In a pivot tbale you can group values by date, by trimester, by year and chose the max, min, mean etc.
    – P. O.
    Feb 6, 2015 at 21:26
  • Seriously Sounds like a job for pivot table. Or a database.
    – Raystafarian
    Feb 8, 2015 at 12:51

1 Answer 1

Reset to default
0

It sounds like you can use an array formula combing MAX and IF. I copied your data and turned it into a table so the formula is easier to follow. The single giant formula looks like this (entered with CTRL+SHIFT+ENTER):

{=MIN(IF(Table1[Month]=1,IF(Table1[Year]=2015,IF(Table1[Value]=MAX(IF(Table1[Month]=1,IF(Table1[Year]=2015,Table1[Value]))),ROW(Table1[Value])))))}

But I recommend splitting it into two just so it's easier to follow (H5 in the second formula refers to the cell where I happened to put the first formula):

{=MAX(IF(Table1[Month]=1,IF(Table1[Year]=2015,Table1[Value])))}
{=MIN(IF(Table1[Month]=1,IF(Table1[Year]=2015,IF(Table1[Value]=H5,ROW(Table1[Value])))))}

The first formula finds the max reading in the given month & year. The second formula finds the first instance of that reading in that month and year. This means that you won't have visibility to any other readings in the same month that happened to be the same.

Note that you can have a table of all the months and change the hard-coded vlaues of 1 and 2015 to be references so you can have the same formula for everything. All of this just gets you the row number with the data in it. After that, you can use INDEX+MATCH or LOOKUP or OFFSET or whatever your favorite lookup function.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .