Author: Oscar Cronquist Article last updated on November 30, 2018

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular month.

Conditional formatting formula (Max)

=MAX(IF((YEAR(\$B\$3:\$B\$16)= YEAR(\$B3))* (MONTH(\$B\$3:\$B\$16)=MONTH(\$B3)), \$C\$3:\$C\$16, ""))=\$C3

Conditional formatting formula (Min)

=MIN(IF((YEAR(\$B\$3:\$B\$16)= YEAR(\$B3))* (MONTH(\$B\$3:\$B\$16)=MONTH(\$B3)), \$C\$3:\$C\$16, ""))=\$C3

### Explaining CF formula (Max)

Step 1 to 3 make sure that values from the correct year and month are extracted based on the date of the current row.

#### Step 1 - Compare year to dates

The YEAR function returns the year from an Excel date.

YEAR(\$B\$3:\$B\$16)= YEAR(\$B3))

becomes

YEAR({41141; 41139; 41131; 41127; 41124; 41121; 41120; 41117; 41116; 41107; 41103; 41095; 41093; 41117})= YEAR(41141))

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

#### Step 2 - Compare month to dates

The MONTH function returns a number representing theÂ month from an Excel date.

MONTH(\$B\$3:\$B\$16)=MONTH(\$B3)

becomes

MONTH({41141; 41139; 41131; 41127; 41124; 41121; 41120; 41117; 41116; 41107; 41103; 41095; 41093; 41117})=MONTH(41141)

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

#### Step 3 - Multiply arrays

Both conditions must be met in order to extract the number, we apply AND-logic if we multiply the arrays.

(YEAR(\$B\$3:\$B\$16)= YEAR(\$B3))* (MONTH(\$B\$3:\$B\$16)

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

and returns

{1;1;1;1;1;0;0;0;0;0;0;0;0;0}.

#### Step 4 - Convert boolean values

The IF function returns row number if the logical expression evaluates to TRUE and nothing "" if FALSE.

IF((YEAR(\$B\$3:\$B\$16)= YEAR(\$B3))* (MONTH(\$B\$3:\$B\$16)=MONTH(\$B3)), \$C\$3:\$C\$16, "")

becomes

IF({1;1;1;1;1;0;0;0;0;0;0;0;0;0}, \$C\$3:\$C\$16, "")

and returns

{3579; 3082; 6883; 3588; 332; ""; ""; ""; ""; ""; ""; ""; ""; ""}.

#### Step 5 - Get largest number from array

The MAX function returns the maximum value from a cell range or array.

MAX(IF((YEAR(\$B\$3:\$B\$16)= YEAR(\$B3))* (MONTH(\$B\$3:\$B\$16)=MONTH(\$B3)), \$C\$3:\$C\$16, ""))

becomes

MAX({3579; 3082; 6883; 3588; 332; ""; ""; ""; ""; ""; ""; ""; ""; ""})

and returnsÂ 6883.

#### Step 6 - Compare the largest value to current value

The equal sign compares the values and returns TRUE if they match.

MAX(IF((YEAR(\$B\$3:\$B\$16)= YEAR(\$B3))* (MONTH(\$B\$3:\$B\$16)=MONTH(\$B3)), \$C\$3:\$C\$16, ""))=\$C3

becomes

6883=3579

and returns FALSE. Row 3 is not highlighted.

### Get Excel *.xlsx file

Highlight-max-and-min-value-in-every-month.xlsx