There is a large amount heading on in these formulaic conditional regulations in Microsoft Excel that emphasize the smallest and major values within just a period of yrs.
Excel’s MINIFS() and MAXIFS() features discover the most affordable and highest values in a selection, respectively, relying on just one or extra problems. If the situation transpires to have ailments of its individual, these functions consider on a new degree of problems. In this posting, I’ll exhibit you two conditional structure procedures that spotlight the minimum and most variety in just a vary of many years. We’re not seeking for a single 12 months as the affliction! Alternatively, it can be any amount of years inclusive on the initially and very last yrs in the array.
SEE: 60 Excel suggestions every user should really master (TechRepublic)
I am applying Microsoft 365 on a Home windows 10 64-bit procedure, but you can use before versions. The browser edition will support these features. For your comfort, you can download the .xlsx demonstration file.
About MINIFS() and MAXIFS()
These two features are tremendous uncomplicated to use, getting the uncomplicated variety
In a nutshell, these functions return possibly the minimum amount or utmost value in a selection exactly where requirements returns legitimate. When a function has several conditions arguments, all must return accurate. Both equally functions function with values and dates, and the values really don’t need to be sorted beforehand.
SEE: How to emphasize exceptional values in Excel (TechRepublic)
Now let’s appear at the data we want to assess in Figure A. The dates in column D are from five several years. Even with out functions it is really effortless to discern that 2020 is the most recent year, and 2010 is the the very least recent. We want to highlight the bare minimum and highest benefit in column D in a period of many years (the standards).
If you observe my content articles, you know that I like to split points down into helper features. You really don’t have to use them but accomplishing so is simple and it helps visualize how anything is effective collectively. Our 1st action is to make enter cells for the initial and last yrs in the calendar year selection: These are in D1:D2. The standards, or affliction, will include things like all the several years inclusive of both equally several years. Mainly because you can find so a great deal going on, we are going to split factors down into easier expressions and then incorporate them to make the conditional structure procedures.
SEE: How to use shortcuts to kind in Microsoft Excel (TechRepublic)
The initial expression returns Genuine or False. When Real, the corresponding day in column C falls in the conditional period of several years Fake suggests the date would not. We are using an AND operator that utilizes equality operators to ascertain irrespective of whether each individual calendar year matches the condition or not.
Enter the very first expression
into F4 and copy to F17. Pay close attention to the relative and absolute references—they matter. If the year in column C is equal to or greater than the Begin 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 Figure B, you can see that there are seven years in 2019 or 2020.
We now know which dates fulfill the year period criteria—the TRUE values in column F tell us that. Next we need to know which value in column D is the highest or lowest, but only evaluating the values when the corresponding values in column F are TRUE.
SEE: 3 ways to suppress zero in Excel (TechRepublic)
The next two expressions, shown in Figure C, return the highest and lowest values inclusive of the two dates, respectively:
The criteria range is the AND operator expression in column F the criteria is the TRUE value. In a nutshell, 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:
You could hide column F, or not. You no longer need the functions in columns G and H—we just worked through those so you could visually work through the logic. Leave them for now so you can watch them update in the next section.
SEE: How to easily include dynamic dates in a Word doc using Excel (TechRepublic)
The conditional rule
Now that we have our two formulaic rules, let’s enter them and see how they work. To get started, select B4:D17 and then do the following:
- On the Home tab, click Conditional Formatting in the Styles group.
- Choose New Rule from the dropdown.
- In the resulting dialog, select the last option, Use a Formula to…, in the top pane
- In the lower pane, enter the expression:
- Click Format, choose a light blue fill color, and click OK.
- Figure D shows the expression and the fill format. Click OK.
As you can see in Figure E, the record with the largest value in column D that falls within the years 2017 and 2018 is in row 6. The MAXIFS() function in column G verifies it. To enter the second rule, repeat the instructions above, entering the expression
during step 4. Figure F shows both rules in place. Now, spend some time, entering different years in the input cells D1 and D2. You can use the updating values in columns G and H to confirm that the rule is working.
If you enter a year that isn’t represented by a date value in column C, the rules continue to work. If neither date is represented, everything continues to work, but you might not realize why. Specifically, neither rule will be satisfied, so no record will be highlighted.