PDA

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

intheblack
08-13-2008, 09:56 PM
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
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

=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
08-14-2008, 08:44 PM
Thanks Mike,
exactly what I was looking for. Cheers