AVERAGE, MAX, MIN, MEDIAN in Excel

Excel is a powerful and versatile spreadsheet application that allows you to perform various calculations and analyses on your data. One of the most common tasks in Excel is to find the average, maximum, minimum, and median of a set of numbers. These are also known as measures of central tendency, which describe the typical or central value of a distribution of data.

In this article, you will learn how to use different Excel functions and formulas to calculate the average, maximum, minimum, and median of a set of numbers in Excel. You will also learn some tips and tricks to make your calculations easier and more accurate.

How to calculate the average in Excel

The average, or arithmetic mean, is the sum of a group of numbers divided by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

To calculate the average in Excel, you can use one of the following functions:

AVERAGE: This function returns the average of a set of numbers. You can enter up to 255 numbers as arguments, or use a range of cells that contain numbers. For example, =AVERAGE(2,3,3,5,7,10) or =AVERAGE(A2:A7) will return 5.
AVERAGEA: This function returns the average of a set of values that can include numbers, text, logical values (TRUE or FALSE), and empty cells. Text and logical values are converted to numbers before calculating the average. Text values are converted to 0 and logical values are converted to 1 (TRUE) or 0 (FALSE). For example, =AVERAGEA(2,"3",TRUE,FALSE,"",10) will return 2.67.
AVERAGEIF: This function returns the average of a set of numbers that meet a single criterion. You can specify the range of cells that contain the numbers, the criterion to filter them, and optionally another range of cells that contain the values to average. For example, =AVERAGEIF(A2:A7,"Banana",C2:C7) will return the average of the values in C2:C7 that correspond to “Banana” in A2:A7.
AVERAGEIFS: This function returns the average of a set of numbers that meet multiple criteria. You can specify up to 127 pairs of ranges and criteria to filter the numbers. For example, =AVERAGEIFS(C2:C7,A2:A7,"Banana",B2:B7,"Delivered") will return the average of the values in C2:C7 that correspond to “Banana” in A2:A7 and “Delivered” in B2:B7.

How to calculate the maximum in Excel

The maximum is the highest value in a set of numbers. For example, the maximum of 2, 3, 3, 5, 7, and 10 is 10.

To calculate the maximum in Excel, you can use one of the following functions:

MAX: This function returns the maximum of a set of numbers. You can enter up to 255 numbers as arguments, or use a range of cells that contain numbers. For example, =MAX(2,3,3,5,7,10) or =MAX(A2:A7) will return 10.
MAXA: This function returns the maximum of a set of values that can include numbers, text, logical values (TRUE or FALSE), and empty cells. Text and logical values are converted to numbers before finding the maximum. Text values are converted to 0 and logical values are converted to 1 (TRUE) or 0 (FALSE). For example, =MAXA(2,"3",TRUE,FALSE,"",10) will return 10.
MAXIFS: This function returns the maximum of a set of numbers that meet multiple criteria. You can specify up to 126 pairs of ranges and criteria to filter the numbers. For example, =MAXIFS(C2:C7,A2:A7,"Banana",B2:B7,"Delivered") will return the maximum of the values in C2:C7 that correspond to “Banana” in A2:A7 and “Delivered” in B2:B7.

How to calculate the minimum in Excel

The minimum is the lowest value in a set of numbers. For example, the minimum of 2, 3, 3, 5,7, and 10 is 2.

To calculate the minimum in Excel, you can use one of the following functions:

MIN: This function returns the minimum of a set of numbers. You can enter up to 255 numbers as arguments, or use a range of cells that contain numbers. For example, =MIN(2,3,3,5,7,10) or =MIN(A2:A7) will return 2.
MINA: This function returns the minimum of a set of values that can include numbers, text, logical values (TRUE or FALSE), and empty cells. Text and logical values are converted to numbers before finding the minimum. Text values are converted to 0 and logical values are converted to 1 (TRUE) or 0 (FALSE). For example, =MINA(2,"3",TRUE,FALSE,"",10) will return 0.
MINIFS: This function returns the minimum of a set of numbers that meet multiple criteria. You can specify up to 126 pairs of ranges and criteria to filter the numbers. For example, =MINIFS(C2:C7,A2:A7,"Banana",B2:B7,"Delivered") will return the minimum of the values in C2:C7 that correspond to “Banana” in A2:A7 and “Delivered” in B2:B7.

How to calculate the median in Excel

The median is the middle value in a set of numbers that are arranged in ascending or descending order. If there is an odd number of values, the median is the middle one. If there is an even number of values, the median is the average of the middle two. For example, the median of 2, 3, 3, 5, 7, and 10 is 4, which is the average of 3 and 5.

To calculate the median in Excel, you can use one of the following functions:

MEDIAN: This function returns the median of a set of numbers. You can enter up to 255 numbers as arguments, or use a range of cells that contain numbers. For example, =MEDIAN(2,3,3,5,7,10) or =MEDIAN(A2:A7) will return 4.
MEDIANIF: This function returns the median of a set of numbers that meet a single criterion. You can specify the range of cells that contain the numbers, the criterion to filter them, and optionally another range of cells that contain the values to find the median. For example, =MEDIANIF(A2:A7,"Banana",C2:C7) will return the median of the values in C2:C7 that correspond to “Banana” in A2:A7.
MEDIANIFS: This function returns the median of a set of numbers that meet multiple criteria. You can specify up to 127 pairs of ranges and criteria to filter the numbers. For example, =MEDIANIFS(C2:C7,A2:A7,"Banana",B2:B7,"Delivered") will return the median of the values in C2:C7 that correspond to “Banana” in A2:A7 and “Delivered” in B2:B7.

Tips and tricks for calculating average, max, min, and median in Excel

Here are some tips and tricks to help you calculate average, max, min, and median in Excel more easily and accurately:

You can use cell references or named ranges instead of typing individual numbers as arguments for the functions. This way, you can avoid errors and update your calculations automatically when your data changes.
You can use wildcards (* and ?) or operators (=,<>,<,>,<=,>=) to specify criteria for filtering your data. For example, =AVERAGEIF(A2:A7,"*ana") will return the average of the values that end with “ana” in A2:A7.
You can use logical functions (AND, OR, NOT) or array formulas to create more complex criteria for filtering your data. For example, =AVERAGE(IF(AND(A2:A7="Banana",B2:B7="Delivered"),C2:C7)) will return the average of the values in C2:C7 that correspond to “Banana” in A2:A7 and “Delivered” in B2:B7. Note that you need to press Ctrl+Shift+Enter to enter an array formula.
You can use conditional formatting to highlight or visualize your data based on average, max, min, and median values. For example, you can use the color scale option to apply a gradient of colors to your data based on their values, or use the icon sets option to display different icons for your data based on their values.

You can use pivot tables or charts to summarize and visualize your data based on average, max, min, and median values. For example, you can create a pivot table that shows the average, max, min, and median sales by product category and month, or create a chart that shows the average, max, min, and median temperature by city and season.

Conclusion

In this article, you have learned how to use different Excel functions and formulas to calculate the average, maximum, minimum, and median of a set of numbers in Excel. You have also learned some tips and tricks to make your calculations easier and more accurate. You can use these skills to perform various calculations and analyses on your data and gain insights from them.

AVERAGE, MAX, MIN, MEDIAN in Excel