-
Solved: Finding Min & Max values by week, month and year
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 :-)
-
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)
-
Thanks Mike,
exactly what I was looking for. Cheers
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules