Priti
We can use Microsoft Excel to perform diverse types of calculations. However, we will demonstrate calculating a dataset’s average, minimum, and maximum. In this article, we described 4 simple ways to calculate average, minimum, and maximum in Excel.
📁 Download Excel File
Download the Excel file used for the demonstration from the link below.
Learn to Calculate Average, Minimum, and Maximum in Excel with These 4 Methods
First, let us become acquainted with our dataset, which will be used as an example throughout the essay. We know the names of the students along with their test scores. Now, we want to determine their average, minimum, and maximum grades. Therefore, we will use this dataset to illustrate four straightforward methods for calculating Excel’s average minimum and maximum values.
1. Using Excel Functions
This method will use excel functions to demonstrate how to calculate average, minimum, and maximum in Excel. Let us go through the steps to apply these functions.
1.1 Calculating Average
This first method uses AVERAGE function to calculate the average of the dataset. The AVERAGE function returns the inputs’ arithmetic mean or average. Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cell where you want the desired result. Here, we are selecting cell C12 and inserting the formula. Finally, we will have the average grades of the students.
=AVERAGE(C6:C10)
📕 Read More: 2 Easy Ways to Compute Average Excluding 0 in Excel
1.2 Calculating Maximum
We will calculate the maximum grade in this method using MAX and LARGE functions. Let us go through the steps.
Using MAX Function
The MAX function returns the largest value from a range. Hence, we will use this function to calculate the maximum in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C12 and insert the formula. Finally, we will have the maximum grade of the students.
=MAX(C6:C10)
Using LARGE Function
The LARGE function returns the n-th largest value from a range. Therefore, we will use this function to calculate the maximum in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C13 and insert the formula. Finally, we will have the maximum grade of the students.
=LARGE(C6:C10,1)
Here, LARGE function looks into the range C6:C10 for the 1st value, which means the largest value in the range, and returns the maximum grade of the students.
📕 Read More: 6 Quick Ways to Calculate Average Percentage of Marks in Excel
1.3 Calculating Minimum
Now, we will calculate the minimum grade in this method using MIN and SMALL functions. Let us go through the steps.
Using MIN Function
The MIN function returns the smallest value from a range. Hence, we will use this function to calculate the minimum in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C12 and insert the formula. Finally, we will have the minimum grade of the students.
=MIN(C6:C10)
Using SMALL Function
The SMALL function returns the n-th smallest value from the range. Therefore, we will use this function to calculate the minimum in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C13 and insert the formula. Finally, we will have the minimum grade of the students.
=SMALL(C6:C10,1)
Here, SMALL function looks into the range C6:C10 for the 1st value which means the smallest value in the range, and returns the minimum grade of the students.
2. Utilizing Auto-Stat Feature
In this second method, we will use the Auto-Stats feature for the average, minimum, and maximum grades. So, let us see the steps to apply this feature.
⬇️⬇️ STEPS ⬇️⬇️
- First, select the cells containing the values for which you want to calculate the average, minimum and maximum. We selected C6:C11. Now at the bottom right of the sheet, we can see the auto stats. Here we have only the average of the values.
- To show maximum and minimum values right-click on the status bar and click on Maximum and Minimum.
- Lastly. the values will be illustrated on the status bar.
📕 Read More: 3 Approaches to Calculate Average of Text in Excel
3. Applying AutoSum Feature
This method will use the AutoSum function in the Ribbon to calculate the average, minimum, and maximum in Excel. Let us see the steps now.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell C12 and go to the Home tab, and click on AutoSum from the Editing Then, to calculate the average, click on the Average option.
- Now, enter the range C6:C10.
- Next, select cell C14 and select MIN from the AutoSum options, and enter the range C6:C10.
- And enter the range C6:C10.
- After that, select cell C13 and select Max from the AutoSum options, and enter the range C6:C10.
- Lastly, the outcome looks like this.
📕 Read More: 7 Easy Ways to Calculate Average Rating in Excel
4. Using Pivot Table
This last method will demonstrate using the Pivot Table for calculating the average, minimum, and maximum in Excel. Let us see the steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select the range B5:C10 and select the Pivot table from the Tables group in the Insert tab.
- Then, a box will pop up where the range is already entered. We are selecting New Worksheet as the location Pivot table to be placed and pressing OK.
- In a new worksheet, we will have PivotTable Fields on the right side of the sheet. Drag the Name field into the Rows area and Marks into the Values.
- Then, click the dropdown sign in the Values area and select Value Field Settings.
- Now, to calculate the average select Average from the Summarize value field by options and press OK and we will have the average of marks.
- Similarly, for the maximum mark, select MAX from the Summarize value field by options and press OK and we will have the maximum mark.
- Lastly, to calculate the minimum select Minimum from the Summarize value field by options and press OK and we will have the minimum mark.
📕 Read More: 6 Ways to Solve When Average Formula is Not Working in Excel
📄 Important Notes
🖊️ While creating Pivot Table, you can select Existing Worksheet and enter your desired location if you want your table to be in the same sheet as your dataset.
🖊️ While applying the Auto-Stats feature, you can customize your status bar by right-clicking and selecting the options according to your need.
📝 Takeaways from This Article
📌 This article demonstrated 4 methods to calculate average, minimum, and maximum in Excel.
📌 In the first method, we used basic Excel Functions for calculating average, minimum, and maximum in Excel.
📌 Then, we demonstrated using Auto-Stats to calculate average, minimum, and maximum in Excel.
📌 Next, we used AutoSum functions for calculating average, minimum, and maximum in Excel.
📌 Finally, we employed Pivot Table to calculate the average, minimum, and maximum in Excel.
Conclusion
This article has demonstrated 4 methods to calculate average, minimum, and maximum in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.
For more guides like this, visit Excelden.com
Related Articles
- 3 Approaches to Calculate 5 Star Rating Average in Excel
- 7 Ways to Calculate Average Attendance with Formula in Excel
- How to Calculate Average Percentage Change in Excel
- 5 Ways to Calculate 7-Day Moving Average in Excel
