Quadra

Connecting Technology and Business.

MAXImise productivity & MINImise confusion - Use MAXIFS and MINIFS

If you’re familiar with statistical functions like COUNTIFS, SUMIFS and AVERAGEIFS, then MAXIFS and MINIFS don’t need much explanation.

The classic MAX and MIN functions calculate the maximum or minimum value in a range, but what if you need to apply conditions to filter your data? This is precisely what MAXIFS and MINIFS allow.

Now you can apply conditions and filter data before calculating with MAXIFS and MINIFS in Excel.

The MAXIFS and MINIFS functions are available if you are an Office 365 subscriber and have the latest version of Office installed on your PC. It is also available in Excel Online and in Excel Mobile or Excel for Android phones and tablets.

 

You can specify one or more conditions that filter the data before calculating the max or min. The conditions can be applied to adjacent ranges or the range that contains the values. For example, let’s say a retailer has a table containing sales data for all their stores. They can use the MAXIFS and MINIFS functions to calculate the maximum and minimum sales figure for a specified item in stores located in a specified region.

 

The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria.

 

Here is the Syntax:

 

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

 

The MINIFS function returns the minimum value among cells specified by a given set of conditions or criteria.

 

Here is the Syntax:

 

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

 

The size and shape of the max_range and criteria_range arguments must be the same, otherwise these functions return the #VALUE! error.

 

In the example shown below, MINIFS and MAXIFS are used to calculate the min and max sales figures from the table, but it only includes values from the Sales column if the value in the Retailer column is “BigMart,” the value in the Brand column is “Longlast” and the value in the Sales column is greater than zero.

 

 

Loading