Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Average, Minimum And Maximum in Excel

Calculating Average, Minimum and Maximum is a common and simple task in Excel. There are several methods to do it. In this article, you will learn how to calculate average, minimum and maximum in Excel with 4 easy methods.

Before going to the next part, let’s have a quick look at the briefs of the methods we are using here and the relevant output from them.

Overview of How to Calculate Average Minimum and Maximum in Excel


Download Practice Book

You can download the free Excel template from here and practice on your own.


4 Easy Methods to Calculate Average, Minimum And Maximum in Excel

Let’s get introduced to our dataset first. I have made a dataset that represents some foods’ prices.

Dataset Overview

Let’s see how we can calculate average, minimum and maximum in Excel considering the price of the dataset.


Method 1: Use Functions to Calculate Average, Minimum And Maximum in Excel

First of all, we will see the use of different Excel functions for serving our purpose.

The AVERAGE function calculates the average (arithmetic mean) of a group of numbers.

The MIN function returns the smallest value from the numbers provided.

And the MAX function returns the highest value in a set of data.

Let’s utilize these functions.

Step 1:

➦ Firstly, in Cell C13 type the formula given below-

=AVERAGE(C3:C9)

➦ Then, hit the ENTER button and the average will be evaluated.

AVERAGE Function to Calculate Average Value in Excel

Step 2:

➦ Afterward, activate Cell C14 and write the formula-

=MIN(C3:C9)

➦ Then, press the ENTER button now to get the minimum value.

MIN Function to Calculate Minimum Value in Excel

Step 3:

➦ Further, type the formula in Cell C15 which is given below-

=MAX(C3:C9)

➦ Then, just hit the ENTER button to find the maximum value.

MAX Function to Calculate Maximum Value in Excel


Method 2: Applying AutoSum Tool

Here, we’ll do the same operation using the Excel AutoSum tool from the Home ribbon. First, I’ll show how to find the average.

Step 1:

➦ Firstly, activate Cell C13.

➦ Then, Click Home > Editing > AutoSum > Average.

AutoSum Tool to Evaluate Average in Excel

Step 2:

➦ Afterward, select the data range and click the Enter button.

Calculate Average Value in Excel

Now, to find minimum and maximum-

➦ Simply, click HOME > Editing > AutoSum > Min/Max and then the other steps are the same.

AutoSum Tool to Evaluate Minimum And Maximum in Excel


Similar Readings:


Method 3: Utilizing Auto-Stats Feature

It is one of the simplest ways to find the average, minimum and maximum.

➦ Here, all you have to do is => just select the data range and Excel will automatically show the Average, Min, and Max values in the status bar of your sheet.

Auto-Stats to Find Average, Minimum And Maximum in Excel


Method 4: Create Pivot Table to Calculate Average Minimum And Maximum

In our last method, we’ll calculate the average, minimum, and maximum by using the Pivot Table. A pivot table in Excel is an extraction or resumé of your original table with source data. A pivot table can provide quick answers to questions about your table.

Step 1:

➦ Firstly, click as follows: Insert > PivotTable.

➦ Consecutively, a dialog box will open up.

Insert Pivot Table

Step 2:

➦ Afterward, select where you will create the table and set the location. I have selected Existing Worksheet and Cell E4 as the location.

➦ Then, press OK.

➦ Instantly, a dialog box named ‘PivotTable Fields’ will appear.

Pivot Table Dialog Box

Step 3:

➦ Further, mark on Products and Price option in the field.

Pivot Table Field Selection

Step 4:

➦ Now, click:  Sum of Price > Value Field Settings.

➦ Value Field Settings dialog box will appear.

Pivot Table Field Settings

Step 5:

➦ Then, select Average from the ‘Summarize Values By’ option and press OK.

Pivot Table to Calculate Average in Excel

See the image below and the average price is now showing in the table.

Average Value with Pivot Table

Step 6:

➦ Now, to find the minimum or maximum just select Max/Min from the ‘Summarize Values By’ option and press OK.

How to Calculate Maximum and Minimum in Excel with Pivot Table


How to Find Average, Minimum and Maximum Value with Condition in Excel

Let’s say, we are in need to calculate average, minimum and maximum values after meeting a certain criterion. We have modified our data. The product: “Milk” has occurred a few times and we want to calculate the average, minimum and maximum price of “Milk” only.

Sample Dataset

For this purpose, we will successfully apply the AVERAGEIF function, and combine IF with MAX and MIN functions.

Step 1:

➦ Firstly, in Cell F5 type the formula given below-

=AVERAGEIF(B5:B14,B6,C5:C14)

➦ Then, hit the ENTER button and the average will be evaluated.

Average Value with Condition

Step 2:

➦ Afterward, activate Cell C14 and write the formula-

=MIN(IF(B5:B14=F4,C5:C14))

➦ Then, press the ENTER button now to get the minimum value.

Minimum Value with Condition

 💡  Formula Breakdown

The IF function looks for the cell value of F4 in the range B5:B14 and returns the corresponding value of F4 from the range C5:C14.

So, Output => {FALSE;15;FALSE;20;FALSE;25;FALSE;30;FALSE;40}

Finally, the MIN function returns the minimum value from the array.

MIN(IF(B5:B14=F4,C5:C14)) = MIN({FALSE;15;FALSE;20;FALSE;25;FALSE;30;FALSE;40}) = 15

So, final Output => 15

Step 3:

➦ Further, type the formula in Cell C15 which is given below-

=MAX(IF(B5:B14=F4,C5:C14))

➦ Then, just hit the ENTER button to find the maximum value.

Maximum Value with Condition


Conclusion

I hope all of the methods described above will be useful enough to calculate the average, minimum, and maximum in excel. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

2 Comments
  1. I’m confused. I download the excel file and send a copy to my drive.
    Maybe anyday you delete this excel file which you share here & i download it then send my drive & save in excel when you delete this file can i there’s a chance to lost my saved this file

    • Hello Mahedi, thanks for your feedback. When you download the file then there’s no connection between your downloaded file and our uploaded file. So, no worries, your file won’t lose.

Leave a reply

ExcelDemy
Logo