Copyright infringement

How to find the minimum and maximum values within a specified set of years in Excel

There is a lot going on in these formulaic conditional rules in Microsoft Excel that highlight the smallest and largest values ​​over a period of years.

Writing in a spreadsheet

Photo: iStock/Rawpixel

The MINIFS() and MAXIFS() functions in Excel determine the lowest and highest values ​​within a range, respectively, based on one or more conditions. If a case has conditions of its own, these jobs take on a new level of difficulty. In this article, I’ll show you two conditional formatting rules that highlight minimum and maximum number within a file group of years. We are not looking for one year as a requirement! Alternatively, any number of years can be inclusive of the first and last years in the range.

See: 60 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use earlier versions. Browser version will support these functions. For your convenience, you can download the .xlsx demo file.

ABOUT MINIFS() and MAXIFS()

These two functions are very easy to use, and take a simple form

MINIFS (MiningAndStandard 1And1 . criteria[,criteriarange2,criteria2], …)

Maximum (maximum rangeAndStandard 1And1 . criteria[,criteriarange2,criteria2], …)

In short, these functions return either the minimum or maximum value within the range where Standards returns true. When a function has multiple criteria arguments, it should all return true. Both functions work with values ​​and dates, and the values ​​do not have to be pre-sorted.

See: How to highlight unique values ​​in Excel (TechRepublic)

Standards

Now let’s look at the data we want to evaluate Figure A. The dates in column D are five years old. Even without the jobs, it’s easy to discern that 2020 is the most recent year, and 2010 is the least recent. We want to highlight the minimum and maximum value in column D over a period of years (the criteria).

Figure A

excelminmaxyear-a.jpg

We will use the MINIFS() and MAXIFS() functions to set a conditional format.

If you follow my articles, you know that I like to break things down into auxiliary functions. You don’t have to use it but doing so is easy and helps visualize how it all works together. Our first step is to create input cells for the first and last years in the year range: these are in D1:D2. The criteria or requirement will include all years including the two. Since there’s a lot going on, we’ll break things down into simpler expressions and then combine them to create the conditional formatting rules.

We see: How to Use Shortcuts to Sort in Microsoft Excel (TechRepublic)

expressions

The first expression returns TRUE or FALSE. When the value is TRUE, the corresponding date in column C falls within the conditional period of years; FALSE means that the date does not. We use AND operator which uses equality operators to determine if each year fits the condition or not.

Enter the first expression

=AND(YEAR($C4)>=$D$1, YEAR($C4)

in F4 and copy it to F17. Pay close attention to relative and absolute references – they are important. If the year in column C is equal to or greater than the start date in D1 And That same year is equal to or less than the end date in D2, this function returns TRUE and FALSE if not. in a Figure BYou can see that there are seven years in 2019 or 2020.

Figure B

excelminmaxyear-b.jpg

Seven dates in 2019 or 2020.

We now know the dates that meet the criteria for the period of the year – the TRUE values ​​in column F tell us this. Next, we need to know which value in column D is the highest or lowest, but only evaluate the values ​​when the corresponding values ​​in column F are TRUE.

We see: 3 ways to prevent scratch in Excel (TechRepublic)

The following two expressions, shown in Figure C, returns the highest and lowest values ​​including the two dates, respectively:

= MAXIFS ($D$4: $D$17, $F$4: $F$17, TRUE)

=MINIFS ($D$4: $D$17, $F$4: $F$17, TRUE)

Figure C

excelminmaxyear-c.jpg

The functions in columns G and H return the highest and lowest values, including dates.

The criteria range is the expression of the AND operator in column F; The criteria is TRUE. In short, the MAXIFS() and MINIFS() functions evaluate only those values ​​in column D where the value in column F is TRUE.

At this point, you have the conditional rules:

=D4 = MAXIFS ($D$4: $D$17, $F$4: $F$17, TRUE)

=D4$=MINIFS ($D$4: $D$17, $F$4: $F$17, TRUE)

Can column F be hidden, or not. You no longer need the functions in columns G and H – we just worked through those functions so you can work visually through the logic. Leave it for now so you can watch it updated in the next section.

See: How to Easily Embed Dynamic Dates in a Word Document Using Excel (TechRepublic)

conditional rule

Now that we have our formula base, let’s go into them and see how they work. To get started, select B4:D17 and then do the following:

  1. On the Home tab, click Conditional Formatting in the Styles group.
  2. Choose a new rule from the dropdown list.
  3. In the resulting dialog, select the last option, Use a formula …, at the top
  4. At the bottom, enter the expression:
    =D4 = MAXIFS ($D$4: $D$17, $F$4: $F$17, TRUE)
  5. Click Format, choose a light blue fill color, and click OK.
  6. figure d Expression and fill format are shown. Click OK.

figure d

excelminmaxyear-d.jpg

Enter the formula base.

as you see in Figure E, the record with the largest value in column D falls within 2017 and 2018 in row 6. The MAXIFS() function checks it in column G. To enter the second base, repeat the instructions above, entering the expression

=D4$=MINIFS ($D$4: $D$17, $F$4: $F$17, TRUE)

during step 4. shape and Shows both bases in place. Now, spend some time, and enter different years into input cells D1 and D2. You can use the update values ​​in columns G and H to confirm that the rule is working.

Figure E

excelminmaxyear-e.jpg

The first rule is in place.

shape and

excelminmaxyear-f.jpg

If you enter a year not represented by a date value in column C, the rules continue to work. If neither date is given, everything will continue to work, but you may not realize why. Specifically, neither rule will be satisfied, so no record will be highlighted.

see also