# Discussion on: Highlight the minimum and maximum values for Excel subsets

9

### Join the conversation!

View:
Show:
1 Vote
###### Easier with pivot table
sparent 29th May
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.
Contributr
###### Not the same, but probably works for some
ssharkins@... 29th May
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.
###### Test
j.smitherman@... 29th May
testing
###### The MONTH(\$A\$2:\$A\$13) part of the formula
plcarr 29th May
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.
1 Vote
###### The MONTH(\$A\$2:\$A\$13) part of the formula
jbenton@... 30th May
all conditonal format formulae are treated as array formulae
###### Ah!
DBlayney Updated - 5th Jun
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.
1 Vote
###### how to tabluate the years' monthly maxima using Excel 2003
jbenton@... 30th May
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
Contributr
###### Discovery
ssharkins@... 4th Jun
That was part of my discovery process!
=\$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.
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.