View Full Version : Solved: Finding Min & Max values by week, month and year

08-13-2008, 09:56 PM
I could use some help on how best to approach this.
I have col A which has dates in daily format (eg 19/03/1999) this could range from 100 rows to 12000 rows
Col B has a value for each of those days

How can I;
A) Firstly sort by week, month and year
B) find the lowest and highest value for each week, month and year.

Any suggestions would be appreciated :-)

08-13-2008, 11:35 PM
A) Sorting column A will sort by day, week, month and year simultaineously.

B)If the target date is in G1, the array formulas


will return the maximum value in column B matching the week, month and year of the date in G1.

The array formula
will do minimums.

Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

08-14-2008, 08:44 PM
Thanks Mike,
exactly what I was looking for. Cheers