Discussion on:
View:
Show:
Those solutions seem pretty awkward. My first thought was to use a pivot table. I would simply group the dates by Month and use Max or Min on the Value field.
If all you need are the max and min values, in separate tables, that's a quick and easy solution. If you need the entire data set, a pivot table won't work.
When I looked into how this works, I was intrigued by the MONTH($A$2:$A$13) part at the heart of the formula.
This returns the Month of the corresponding cell in the same row in MONTH$A$2:$A$13.
It's a bit like an array formula without the { }.
Is this standard Excel behaviour? I haven't noticed it before.
This returns the Month of the corresponding cell in the same row in MONTH$A$2:$A$13.
It's a bit like an array formula without the { }.
Is this standard Excel behaviour? I haven't noticed it before.
all conditonal format formulae are treated as array formulae
I am a great fan of conditional formatting but I had not realised that formulae are always treated as array formulae. This is an importnat piece if learning for me.
What I do not understand is how the MIN function ignores the FALSE values in the implied array where the month does not match. FALSE is numerically zero and lower than the other values.
What I do not understand is how the MIN function ignores the FALSE values in the implied array where the month does not match. FALSE is numerically zero and lower than the other values.
Define a named range "dates" to be =OFFSET($A$1,1,0,COUNT($A:$A))
Define a named range "values" to be =OFFSET(dates,0,1)
Fill D2:D13 with 1/1 to 1/12, formatted as MMM (so will appear as Jan-Dec)
In E2 enter =MAX((MONTH(D2)=MONTH(dates))*values) as an array (ie with CTRL-SH-enter instead of just enter)
Fill this down to E13
Continue to add data to columns A:B for the rest of the year
Define a named range "values" to be =OFFSET(dates,0,1)
Fill D2:D13 with 1/1 to 1/12, formatted as MMM (so will appear as Jan-Dec)
In E2 enter =MAX((MONTH(D2)=MONTH(dates))*values) as an array (ie with CTRL-SH-enter instead of just enter)
Fill this down to E13
Continue to add data to columns A:B for the rest of the year
=$B2=MAX(IF(MONTH($A2)=MONTH($A$2:$A$2521),$B$2:$B$2521))
this formula works perfectly for each month but how do I make it do it by year and month? Right now it just highlights the max in the highest month of all years combined. I have about 2600 lines I have to highlight each max and min value for each month of each year.
this formula works perfectly for each month but how do I make it do it by year and month? Right now it just highlights the max in the highest month of all years combined. I have about 2600 lines I have to highlight each max and min value for each month of each year.
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
