## How to highlight MAX and MIN value based on month

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)

Conditional formatting formula (Min)

### 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.

### Download Excel *.xlsx file

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

Highlight smallest duplicate number

Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Highlight dates in a date range

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

### 7 Responses to “How to highlight MAX and MIN value based on month”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Confused :( Both formulas looks like same. do i have to replace MAX part in second formula with MIN ?? . Also guide me how to use them, where should i insert the formula?

Thank you very much Oscar

srini

srinivas,

Confused Both formulas looks like same. do i have to replace MAX part in second formula with MIN ??Yes, you are right! It is now corrected.

Also guide me how to use them, where should i insert the formula?It is a conditional formatting formula.

How to apply the conditional formatting formula in excel 2007:

1. Select cell range B2:B159

2. Go to tab "Home"

3. Click "Conditional formatting" button

4. Click "New rule..."

5. Click "Use formula to determine which cells to format"

6. Type formula

7. Click "Format..." button

8. Select a color.

9. Click OK twice.

Repeat steps and use the second formula and preferbly another color.

Thank you very much Oscar. It is now clear :)

hey this formula works perfectly for each month but how do I make it do it by year and month? I have about 2600 lines I have to highlight each max and min value for each month of each year.

Pradeep,

Conditional formatting formula min value:

Conditional formatting formula max value:

Download excel *.xlsx file

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

Dear Oscar,

How can I do this for the repeating textual items in column A instead of dates? For example, I would like to determine min values for pears, apples, oranges, whereas the series looks like this:

Pear 5.5

Pear 1.2

Apple 3.2

Apple 5.5

Pear 1.2

Orange 6

Apple 3.2

Orange 5.5

Pear 1.2

Thank you in advance.

Best regards,

Goranka

Goranka

Great question.

Array formula in cell E1:

=MIN(IF(D1=$A$1:$A$9,$B$1:$B$9,""))

Array formula in cell E2:

=MIN(IF(D2=$A$1:$A$9,$B$1:$B$9,""))

Array formula in cell E3:

=MIN(IF(D3=$A$1:$A$9,$B$1:$B$9,""))