I finished utilizing the Excel filter button to seek out excessive and low values. These 2 features do it higher

Excel’s filter button is helpful, however I’ve realized I used to be counting on it for the fallacious job. When all I wished was the very best or lowest quantity that matched a sure situation, filtering meant hiding rows, sorting information, after which resetting every little thing afterward. But two features I not too long ago stumbled throughout have given me a a lot cleaner option to get these solutions.

Find excessive and low values with out filtering your information

Let Excel discover the reply for you

MAXIFS and MINIFS aren’t simply two extra features so as to add to your Excel toolbox. Since I discovered about them, I’ve genuinely used them in a number of spreadsheets, and so they’ve quietly turn into my go-to each time I would like to seek out the very best or lowest worth that meets sure circumstances.

There’s good motive for that. The outdated approach of doing that is normally fairly easy: filter your information till you’ve got bought the information you need, kind the related column, and seize the quantity on the prime or backside. It works, however you are altering your view of the dataset simply to reply a single query. If all I need is a solution, why rearrange my information to get it?

MAXIFS and MINIFS go away every little thing the place it’s and return the reply in a cell. That means the outcome can sit in a abstract desk or dashboard, feed one other components, or energy an Excel chart, whereas the underlying dataset stays absolutely seen.

The formulation additionally replace when the supply information adjustments. If you add a brand new document, or an present worth adjustments, Excel recalculates the outcome with out you having to recollect which filters you utilized or kind the info once more.

And then there’s the largest benefit: a number of standards. You can ask Excel to seek out the very best or lowest worth the place a number of circumstances are true on the identical time. Instead of filtering one column, then one other, and eventually sorting what’s left, you possibly can describe the query in a single components.

MAXIFS finds the very best worth that meets your standards

The syntax tells you nearly every little thing that you must know

The fantastic thing about MAXIFS and its partner-in-crime is that their syntax makes them very easy to be taught.

The syntax for MAXIFS is:

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

In plain English, meaning: discover the very best worth in a single vary, however solely embody values the place the corresponding cells meet the desired standards.

So, for instance, this is a desk of pc specs.

Let’s say I’m purchasing for a brand new pc, and I’ve determined I do not wish to spend greater than $1,500. Before I begin evaluating particular person fashions, I wish to understand how a lot RAM I can anticipate to get for that funds. I might filter the Price column to indicate computer systems costing $1,500 or much less, kind the RAM column from largest to smallest, and skim the primary outcome. I desire to ask MAXIFS immediately:

=MAXIFS(tblComputers[RAM], tblComputers[Price], "<=1500")

Here’s what’s occurring:

• tblComputers[RAM] is the max_range—the column containing the numbers I need Excel to seek out the utmost from.
• tblComputers[Price] is the criteria_range. That’s the column Excel checks to resolve which rows qualify.
• “<=1500” is the standards. It tells Excel that the corresponding worth should be lower than or equal to $1,500.

The result’s 32GB, giving me a fast concept of the very best quantity of RAM obtainable inside my funds. I can then use that as a place to begin after I start evaluating particular person computer systems.

And the 2 ranges do not must be totally different. If I wished to seek out the most important quantity in A2:A100 that was itself better than 50, for instance, I might use:

=MAXIFS(A2:A100, A2:A100, ">50")

The key factor to recollect is that max_range tells Excel the place to get the reply, whereas criteria_range and standards inform it which values to think about.

Things get fascinating while you add one other situation

The actual attraction of MAXIFS turns into clear when one situation is not sufficient. For instance, what if I wish to understand how a lot RAM I can get from a Dell pc costing $1,500 or much less?

I can merely add one other criteria_range–standards pair:

=MAXIFS(tblComputers[RAM], tblComputers[Price], "<=1500", tblComputers[Manufacturer], "Dell")

Now Excel has two circumstances to examine. The worth should be $1,500 or much less, and the producer should be Dell.

This is the place MAXIFS begins to exchange the filter button in a way more significant approach. I haven’t got to filter one column, then one other, and eventually kind no matter stays. I can describe the query immediately within the components.

I can hold including standards, too. For instance, I might discover the utmost RAM in a Dell pc costing $1,500 or much less that additionally has a minimum of 512GB of storage:

=MAXIFS(tblComputers[RAM], tblComputers[Price], "<=1500", tblComputers[Manufacturer], "Dell", tblComputers[Storage], ">=512")

The components now has three criteria_range–standards pairs. Excel solely considers a row if all three circumstances are happy, then returns the most important RAM worth amongst these rows.

MINIFS finds the bottom worth that meets your standards

The identical concept works while you’re searching for the smallest quantity

If MAXIFS finds the highest worth that meets your standards, MINIFS finds the lowest. Its syntax is equally straightforward to get your head round:

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

This time, I’ve a desk of race outcomes. I wish to discover the quickest recorded 5K time for runners aged 40–49.

Because a sooner race time is a smaller quantity, MINIFS is precisely what I would like:

=MINIFS(tblRaces[Time], tblRaces[Event], "5K", tblRaces[Age Group], "40-49")

Here, tblRaces[Time] is the min_range, whereas the Event and Age Group columns present the 2 standards pairs. Excel appears to be like solely at 5K outcomes for the 40–49 age group, then returns the shortest time.

And, simply as with MAXIFS, I can add extra criteria_range–standards pairs if I would like them. For instance, I might additionally limit the outcomes to a selected gender or location.

Making the factors changeable

So far, I’ve hard-coded the criteria into my components, however in a real-world spreadsheet, I’d in all probability put these standards in cells as an alternative. Suppose H2 incorporates the occasion and I2 incorporates the age group. I can reference these cells like this:

=MINIFS(tblRaces[Time], tblRaces[Event], H2, tblRaces[Age Group], I2)

Now I can change H2 from 5K to 10K, or I2 from 40-49 to 50-59, and the outcome updates mechanically. I haven’t got to edit the components every time I wish to ask a barely totally different query.

If I additionally wish to see who recorded that point, I can use the FILTER function within the cell subsequent to my MINIFS outcome:

=FILTER(tblRaces[Runner],tblRaces[Time]=J2)

Now I’ve each the quickest time and the runner who recorded it. If two runners occur to share that point, FILTER returns each names.

Some of essentially the most helpful Excel features are hiding in plain sight

MAXIFS and MINIFS are good examples of why it is price often looking beyond the Excel functions you already use. You may need been utilizing Excel for years with out encountering them, but they could possibly be precisely what you want for an issue you are fixing in your subsequent spreadsheet.