How to Calculate Mean (Average), Median & Mode in Excel

In numerical data analysis, there are 3 main measures of central tendency you need. These are: Average (Mean), Median and Mode. They are all standard measures of central location, but each of them is used in different instances to measure a central point. In this article you will learn how to calculate the mean, mode and median of a set of numerical data or grouped data in Microsoft Excel. I used Microsoft Office Excel 2013 for illustration, but you can use any other version, like 2010, 2016, etc.

How to Calculate Average in Excel

Average is also known as Arithmetic mean. It is the most popular of the 3 measures of central tendencies. To calculate the mean of a group of numbers, you simply add them up and then divide the total by count of these numbers.

For instance, to calculate the mean of these numbers: {10, 20, 30, 40, 50}; Add them up and then divide the total by 5.

This is a manual way to calculate mean. But there are built in functions in MS Excel that can help you calculate mean faster.

Depending on your need, Excel has different built in Average functions.

AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS

I will explain each of these functions.

Excel AVERAGE Function

Use this average function if you want to calculate the average of a set of numbers in cells.

The syntax is:

=AVERAGE(number1, [number2], …) or AVERAGE(cell references or cell range)

Note that in the first syntax, the first argument (number1) is required, while the subsequent ones are optional. You can include up to 255 arguments.

Examples of cell references are A2, B2, C3, etc. Examples of cell range is A2:A5, B2:B5, C2:C5, etc.

From the workers' details in the above screenshot, to calculate the average salary of all the worker, type the following formula:

=AVERAGE(D2:D11) then press Enter key. It will return $1,385 in the cell.

NOTE:

When using the AVERAGE function, cells containing zero values are counted, while blank cells are not counted.
Also, cells containing text string and boolean values are neglected. But if you type boolean values directly into the AVERAGE, they are counted. The boolean value - TRUE is equal to 1, while the boolean value - False is equal to 0. Example, the formula =AVERAGE(TRUE, FALSE) will returns 0.5. This is the average of 1 and 0.

See:

Excel AVERAGEA Funtion

Use this average function If the cells contain different types of data like number, text and boolean values (true and false).

The syntax is:

=AVERAGEA(value1, [value2], …)

Where value1, value2, … are values, arrays, cell references or ranges that you want to calculate the average. The first argument is required, while the other (up to 255) arguments are optional.

When using the AVERAGEA formula, the following should be noted:

Empty cells are ignored.
Text values returned by other formulas, including empty string are evaluated as 0.
Boolean value TRUE is evaluated as 1, while boolean value FALSE is evaluated as 0.

For example, the formula =AVERAGEA(5,FALSE) returns 2.5, which is the average of 5 and 0. The formula =AVERAGEA(5,TRUE) returns 3, which is the average of 5 and 1.

NOTE:

If you do not want the Boolean values and text strings to be included in your calculations, use the Excel AVERAGE function.

Excel AVERAGEIF Function

To find the average of a set of numbers based on a single criterion, use this average function.

The syntax is:

AVERAGEIF(range, criteria, [average_range]) then press Enter key.

Where:

Range is the range of cells where the given criteria will be tested.
Criteria is the condition used to determine which cells to include when calculating the average. The criteria can be supplied in the form of a number, logical expression, text value, or cell reference. For example, 2, ">2", "cat", or B2.
Average_range (Optional) is the cells you actually want to calculate their average. If omitted, Excel will calculate an average of the values in the range argument.

For example, if we want to calculate the average of only the male workers in the above screenshot, type the following formula:

=AVERAGEIF(B2:B11, "male", D2:D11) then press Enter key.

It will return $1,440.

AVERAGEIFS

To find the average of a set of numbers based on more than one criterion, use this average function. This function is the plural form of AVERAGEIF.

The syntax is:

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Where:

Average_range is the range of cells that you want to average.
Criteria_range1, criteria_range2, … are the ranges where the specified criteria will be tested. It accepts up 127 arguments. Criteria_range1 is required, while the subsequent ones are optional.
Criteria1, criteria2, … are the criteria that tells Excel which cells to include when calculating the average. You can supply the criteria in the form of a number, logical expression, text value, or cell reference. Criteria1 is required, subsequent criteria are optional.

For example, to calculate the average salary of female workers who are married, type the following formula:

=AVERAGEIFS(D2:D11, B2:B11, "female", C2:C11, "married") then press the Enter key.

The formula returns $1,600.

You can learn more about excel average functions here.

Check:

How to Calculate Median in Excel

Median is simply the middle value in a group of numbers when they are arranged in ascending or descending order.

For example, the median of the following set of numbers: {1, 2, 2, 3, 4, 4, 5} is 3.

Finding the median of an odd number of values in a group is easier. But for even number of values, the medain is the average of the two middle values.

For example, to calculate the medain of these set of values: {1, 2, 2, 3, 4, 4, 5, 5}; you take the average of the 4th and 5th values in the data set.

That is, (3+4)/2 = 3.5

The syntax for median function is Excel is:

=MEDIAN(number1, [number2], …) then press the Enter key.

Where:

Number1, number2, … are numeric values you wish to find their median and can be up to 225 arguments. These can be numbers, dates, named ranges, arrays, or references to cells containing numbers. Number1 is required, while the subsequent numbers are optional.

The following are worth noting for Excel median calculation:

Cells with zero value are included in calculation.
Empty cells and cells containing text and boolean values are not included in calculation.
If you type the boolean values directly as arguments in the MEDIAN function, they are counted. The boolean TRUE value is evaluated as 1, while the boolean FALSE value is evaluated as 0. For example, the formula =MEDIAN(TRUE,FALSE,5,6,7) returns 5, which is the median of the numbers {1, 0, 5, 6, 7}.

From the above screenshot of the workers' detail, to calculate the median of the workers' salaries, type the following formula:

=MEDIAN(D2:D11) then press the Enter key.

The formula will return $1,350.

To confirm the answer, you can sort the data in the cells which you wish to find their median either in ascending or descending order. Since number of our data set was even (10), Excel returned the average of the 5th and 6th data (i.e. $1,300 and $1,400) which is equal to $1,350.

You can learn more about calculating median in excel here.

See:

How to Calculate Mode in Excel

Mode is the value in a data set that has the highest frequency or occurrence. You don't need to perform any calculation to find the mode of a data set. All you need to do is to count the number of times each value occurs in the data set.

For example, the mode of the set of values {1, 2, 2, 3, 3, 4, 4, 4} is 4.

The syntax for mode function is Excel is:

=MODE(range) or =MODE(number1, [number2], ...)
then press Enter key.

Where:

Range or number1, [number2], ... is the range of cells or data set you wish to find their mode.

For the workers' details in the above screenshot, to find the mode of the workers' salary, type the following formula:

=MODE(D2:D11) then press the Enter key.

The formula will return #N/A error because all the values in the cell range occurred only once. So there is no mode.

NOTE:

If Excel discovers that there are two or more modes in your data set, the Excel MODE function will return the lowest mode.

Check:

Conclusion

You have learnt how to calculate mean (average), median and mode in any version of Microsoft Excel. You also learnt how to calculate these measures of central tendencies manually in this article. Feel free to apply these tips in any data set of your choice.

Help your social media friends to discover this amazing Excel tutorial by clicking a share button below.

Drop your comments below. Also click Here to subscribe to our blog via email so that we will update you when new WordPress themes are released by these sites.