How to Calculate Mean, Median and Mode in Excel

The mean (also called average) of the data set is calculated by adding all the numbers in the data set and then dividing it by the total number of values in the data set. In this article, we will find out how to calculate the mean in Excel.

What is the Mean in Excel?

The Mean, also known as the Average, is a fundamental statistics measure that represents the central tendency of datasets.

How to Calculate Mean in Excel

The Arithmetic mean, commonly known as the average is likely a familiar concept to you. This measure is determined by summing a set of numbers and then dividing the total by the Count of those numbers:

For example, the numbers {1,2,2,3,4,6}. To compute the mean, you add these numbers together and then divide the sum by 6, resulting in 3: (1+2+2+3+4+6)/6=3.

In Microsoft Excel, you can calculate the mean using one of the following functions:

AVERAGE: This Function returns the average of a range of numbers.

AVERAGE: This Function provides the average of cells containing various types of data, including numbers, Boolean values, and text.

AVERAGEIF: When you need to find the average based on a single criterion, this function can be used.

AVERAGEIFS: It can be used for calculating the average based on multiple criteria, you can employ this function.

AVERAGE function

In this method, we are going to use the AVERAGE function which returns the mean of the arguments. For example, the =AVERAGE(A1:A10) returns the average of the numbers in the range of A1 to A10.

Syntax:

AVERAGE(number1,[number2],…)

Here,

• number1 (Required): The first cell reference or number for which you want the average
• number2 (optional): Additional cell references or numbers for which you want the average. The maximum limit is 255.

Notes:

• arguments can either be numbers or names, ranges, or cell references that contain numbers.
• If the argument contains text or logical values or empty cells then those values are ignored. However, if the cell contains the value zero then it is included.
• if the argument contains error values or text that cannot be translated to numbers then it will cause errors.
• AVERAGE: This function returns the average of cells with any data (logical values or text representation of numbers)
• AVERAGEIF: This function is used to calculate the average of only the values that meet a single criterion.
• AVERAGEIFS: This function is used to calculate the average based on multiple criteria.

Example:

Let us apply the above formula on some rough data as shown below:

Functions to calculate Mean

In the above example, we have used 4 different functions.

• =AVERAGE(c4:e12): Returns the average of the numbers in cells c4 to e12
• =AVERAGE(c4:f6,6): Computes the average of the values in cells C4 to F6 along with the number 6.
• =AVERAGE(b4:b12, “sara”, c4:c12): Calculates the average of the numbers in cells C4 to C12 by considering the instances where the name “Sara” appears within the range B4 to B12.
• =AVERAGE(d4:d12,b4:b12,”sara”,g4:g12,”b”): Determines the average of the numbers in cells D4 to D12, taking into account two conditions: occurrences of the name “Sara” within the range B4 to B12, and occurrences of the grade “B” within the range G4 to G12. .

How to Calculate Mean Using Keyboard shortcuts

In this method first, you need to select the cells for which you have to calculate the average. Then select the INSERT function from the formulas tab, a dialog box will appear.

Insert Function

Select the AVERAGE function from the dialog and enter the cell range for your list of numbers in the number1 box. For example, if you need values from column A and from row 1 to row 10 then simply enter A1:A10. You can also simply drag and drop the selected cells and click OK. The result will be shown in the cell you have selected.

Functions Argument

What is Median in Excel?

Median helps us to find the middle number in a bunch of numbers. It’s the number that sits right in the middle when you put all the numbers in order from smallest to biggest. This middle number divides the group into two halves, with half the numbers being smaller and half being bigger.

In Microsoft Excel, you can find the median using the MEDIAN function. To illustrate, if you want to determine the median of all the sales amounts in our report, apply this formula:

How to Calculate Meadian in Excel?

Follow the below steps to calculate Median in Excel:

Step 1: Enter your data

Data Entered

Step 2: Select a Cell

Choose an empty cell within your worksheet where you’d like the median value to be displayed.

Cell Selected

Step 3: Use the MEDIAN Function

In the selected cell, type “=MEDIAN(“

Enter the MEDIAN Function

Step 4: Select Your Data Range

Now, select the range of cells that contain your data. In this example, you would select cells A1 to I1. You can either manually type this range or click and drag to select it.

Select the Data range

Step 5: Close the Parenthesis and Press Enter

Once you’ve chosen your data range and closed the parenthesis, press Enter. Excel will then compute the median of the data you selected and show the result in your chosen cell.

Step 6: Preview the Result

Preview the Result

This method works well when dealing with an odd number of values in a dataset. However, what if you have an even number of values? In such a scenario, the median is the average (arithmetic mean) of the two middle values.

For example in the Below example, there are 10 values in the data set.

10 Values in the Dataset

Repeat all the steps as of above and preview the result in the Below image:

Preview the Result

What is Mode in Excel

The mode represents the number that appears most frequently within a given set of numbers. In simpler terms, it’s the number that occurs the most times in the set.

How to Find Mode in Excel

Among all the statistical measures, finding the mode is the simplest and requires the least mathematical computation. Essentially, you identify the mode by locating the score that appears most frequently in a dataset. Here’s how you do it:

Step 1: Enter the Data Set

Data Set

Step 2: Select the Cell where you want the result to be displayed

Select the cell and Enter the Formula

Step 3: Enter the Formula and Press Enter

=MODE(A1:A9)

Step 4: Preview Result

Preview the Result

FAQs

What is Mean in Excel?

The Mean, also known as the Average, is a fundamental statistics measure that represents the central tendency of datasets.

How to calculate the Mean in Excel?

To calculate the mean in Excel, follow the below steps:

Step 1: Enter your data values in a column.

Step 2: Use the formula ‘=AVEARGE(range)” where “range” refers to the range of cells containing your data.

What if my datasets contain empty cells or errors?

Empty cells or errors in your dataset are typically ignored by Excel when calculating the mean using the AVERAGE function.

What is the shortcut way to calculate the Mean in Excel?

Yes, a quick way to calculate the mean in Excel is by using the AutoSum button. Simply select an empty cell below your dataset, click the AutoSum button, and Excel will automatically calculate the mean of the selected range.