Calculate The Highest And Lowest Values In A Data Set.

Today’s Excel tip is a question I was asked recently by a subscriber. It was to achieve an easy way to find the lowest and highest selling prices for a range of products. So, Welcome back to another blog post in my Excel tips #FormulaFriday 2019 series. The data set contained sales records for Sales People along with selling prices of goods. The minimum and the maximum price that the Salesperson agreed with the customer per product was needed. This is an easy job for the MINIF and the MAXIF Excel functions.

Find The Lowest And Highest Selling Prices.

So, let’s start with an extract of the type of data set that was used. This is significantly smaller than the original data set which held around 5000 plus records every month. But, the basis of the calculation is still the same.

use minifs and maxifs in Excel TO GET HIGHEST AND LOWEST SELLING PRICE

Using Excel MINIFS Function

First, we can look for the smallest, lowest of the minimum selling price that a product sold for. In summary, the MINIFS function returns the smallest numeric value that meets one or more criteria in a range of values. It can be used with criteria which can be dates, numbers, and even logical operators such as >,<,= and also wildcards. The syntax is

=MINIFS (min_range, range1, criteria1, [range2], [criteria2], …)

where the arguments are as follows-

min_range – Range of values used to determine the minimum.
range1 – The first range to evaluate.
criteria1 – The criteria to use on range1.
range2 – [optional] The second range to evaluate.
criteria2 – [optional] The criteria to use on range2.

So, we can apply this to our sample data set with the formula as below. The minimum range is E2 to E13. The first (or only range in this example).

MAXIFS AND MINIFS Excel function to find the lowest selling price

In this first example, I have only used the one criteria which is the Product Code. Excel has returned the Minimum Selling Price in our data range which is 13.98. Let’s add in another level, and find the Minimum Selling Price for each of Sales Person or Sales ID. Here all we do is add in another criteria and criteria range. So let’s take a look at the minimum selling price for FIRESAX for Sales Person SUSANB.

MINIFS and MAXIFS To Find lowest
and highest selling price

SUSANB has several sales prices for FIRESAX but the lowest or minimum is 3.99 in the data set. Yes, it is as easy as that to solve this problem. We know the Minimum price but what about finding the Maximum price. Well, yes you guessed it, we just need to use the MAXIFS function.

Using Excel MAXIFS Function.

The MAXIFS function returns the largest numeric value that meets one or more criteria in a range of values. It can be used with criteria which can be dates, numbers, and even logical operators such as >,<,= and also wildcards. The syntax is

=MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)

where the arguments are as follows-

max_range – Range of values used to determine the minimum.
range1 – The first range to evaluate.
criteria1 – The criteria to use on range1.
range2 – [optional] The second range to evaluate.
criteria2 – [optional] The criteria to use on range2.

So, again we can apply this to our sample data set with the formula as below. The maximum range is E2 to E13. The first (or only range in this range).

HIGHEST AND LOWEST SELLING PRICE WITH Excel MAXIFS AND MINIFS Function

In this first example, I have only used the one criteria which is the Product Code. Excel has returned the Maximum Selling Price in our data range which is 15.99. Let’s add in another level, will we?. We will find the maximum Selling Price for each Sales Person or Sales ID. Here all we do is add in another criteria and criteria range. So let’s take a look at the maximum selling price for FIRESAX for Sales Person SUSANB.