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

[intheblack]

Hi,
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 :-)

[mikerickson]

A) Sorting column A will sort by day, week, month and year simultaineously.

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

=MAX(--(WEEKNUM(A1:A100)=WEEKNUM(G1))*B1:B100)
=MAX(--(MONTH(A1:A100)=MONTH(G1))*B1:B100)
=MAX(--(YEAR(A1:A100)=YEAR(G1))*B1:B100)

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

The array formula
=MAX(IF(0<(--(MONTH(A1:A1A100)=MONTH(G1))*B1:B100),B1:B100,9E+99))
will do minimums.

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

[intheblack]

Thanks Mike,
exactly what I was looking for. Cheers