How to calculate Mean in Excel?

[vishrut-singhal]

How to calculate Mean in Excel

As we know, Microsoft Excel document is used to organize and analyze a large amount of data quickly. It also allows us to perform numeric analyses such as calculate the Mean, Median, Mode, and more.

The arithmetic mean is also knows as Average. It is calculated by adding all the numbers and then divide by the sum count of those numbers. For example mean of 2, 8, 16, and 24 is= 12.5.

Uses of Mean

A list of uses of Mean is given below -

• Mean is used to compare historical data.
• It is used to determine Key Performance Indicators (KPIs) as well as quotas.
• It is used to guide business strategy and project management.
• It is used to find the midpoint for the given data sets.
• It helps us to find an average of a group of data sets.

Methods to calculate Mean in Excel

There are the following three methods to calculate Mean in Excel -

1. Calculate Mean in Excel using AVERAGE function
2. Calculate Mean in Excel using SUM and COUNTA function
3. Calculate Mean in Excel using SUM and ROWS function

Method 1: Calculate Mean in Excel using AVERAGE function

In Microsoft Excel, we use the AVERAGE function to calculate the Mean of the given data. It is the easiest and most commonly used method to calculate Mean in Excel document. There is 255 individual input argument in the AVERAGE function. An input argument can be a number, cell reference, range, array, as well as constant.

There are the following AVERAGE functions are used to calculate Mean in Excel -

1. AVERAGE - AVERAGE function returns an Average of given numbers.
2. AVERAGEA - AVERAGEA function returns the Average of cells with any data. (data can be numbers, Boolean, as well as text values).
3. AVERAGEIF - This function is used to find the Average of numbers based on a single criteria.
4. AVERAGEIFS - This function is used to find the Average of numbers based on multiple criteria.

Formula to calculate MEAN in Microsoft Excel document

The below formula is used to calculate the Mean in the [Microsoft Excel] document -

=AVERAGE(B2:B15)

Using the above formula, you can calculate the Mean for rows 2 to 15 in column B.

Note: You can also select cells based on your requirement.

Steps to calculate Mean in Excel using AVERAGE function

There are the following steps to calculate the Mean in Excel document -

Note: Using the below steps, you can calculate Mean in Microsoft Excel 2010, Excel 2013, Excel 2016, and Microsoft Office 365.

Step 1: Open a new or an existing Microsoft Excel document by double-clicking the Excel icon.

Step 2: Enter the data into a Microsoft Excel document. In this example, we are going to calculate the Mean value for the marks obtained by a student in various subjects. See the screenshot below.

Step 3: Once your required data have been entered, place a cursor on the cell where you wish to calculate the Mean (in our case, we use the cell B9)

Step 4: Type the equal sign (=) before AVERAGE(number1, [number2],…) in the selected cell. In our case, we use =AVERAGE(B2:B8). As shown in the screenshot given below.

Note 1: In our case, B2 is the cell from where our data starts and B8 is the cell where our data ends.

Note 2: AVERAGE function automatically ignore empty cells that do not contain any numbers.

Step 5: Once your AVERAGE function is completed, press the Enter key from the keyboard.

The below screenshot shows that the calculated Mean will appear in your selected cell. (In our case, the calculated Mean will appear in cell B9).

Method 2: Calculate MEAN using SUM and COUNTA function

It is an advanced method to calculate MEAN. In this method, the SUM function is used to sum all the given numbers and the COUNTA function is used to count the quantity.

Note: This method is generally used when Text Value as well as numeric value present in the given data set.

In Microsoft Excel, use the below formula to calculate MEAN using the SUM and COUNTA function :

=SUM(B2:B6)/COUNTA(A2:A6)

In the above formula, A and B denote that our date present in the column A and B.

2 and 6 denote that data lies between 2 to 6.

Steps to calculate MEAN using the SUM and COUNTA function

Follow the below given easiest steps to calculate MEAN using SUM and COUNTA function -

Step 1: Open a Microsoft Excel document by double-click on the Microsoft Excel icon.

Note 1: If you want to open a new worksheet, then click on the File -> New -> Blank document.

Note 2: If you want to open an existing document, then click on the File -> Open -> Browse your desired file location -> click on the Open button.

Step 2: Enter data in a Microsoft Excel document to calculate the Mean as shown in the screenshot.

Step 3: Place the cursor on the cell where you want to see the calculated Mean.

Note: In our case, we use the cell B9 to display the calculated Mean.

Step 4: Place the cursor in the selected cell and type =SUM(B2:B6)/COUNT(A2:A6) in the cell as shown in the screenshot given below.

Note 1: In our case, we use the SUM function to sum the weight i.e, (30+40+45+50), and use the Count function to count the total number of Fruits.

Step 5: Press the Enter key to complete the SUM and COUNTA function.

Now, you can see that the calculated Mean will appear in your selected cell. See the screenshot below.

Note: In our case, the calculated Mean result 165/5 = 33 will appear in cell B7.

Method 3: Calculate the Mean in Excel using SUM and ROWS function

This method is almost similar to the calculated Mean using SUM and COUNTA function. In this method, the SUM function adds all numeric values in the given data range and the ROWS function will calculate the number of rows present in the particular interval.

Formula to calculate Mean using SUM and ROWS function

=SUM(B2:B6)/ROWS(B2:B6)

In the above formula, B2:B6 represents that our data present in column B, and its range lies between 2 to 6.

Steps to calculate Mean using SUM and ROWS function

Follow the below given easiest steps to calculate Mean using SUM and ROWS function -

Step 1: Open a new or an existing Microsoft Excel document.

Step 2: Prepare an Excel document with data to calculate the Mean.

Step 3: Place the cursor in the cell where you want a result to appear (in this example, we use the cell B7)

Step 4: Type formula =SUM(B2:B6)/ROWS(B2:B6) in the selected cell as shown in the screenshot given below.

Note: In our case, we use the SUM function to sum all the weight i.e. (30+40+45+50), and use the ROWS function to count the total number of Rows present in the document.

Step 5: Press the Enter key from the keyboard.

The screenshot below shows the result of the calculated MEAN in your selected cell.

Note: In our case, result 33 will appear in our selected cell, i.e., B7.

[stevefoden-06638680f0d5 (Steve Foden)]

Microsoft Excel is essential for organizing and analyzing data swiftly. It excels in numeric analysis, calculating metrics like Mean, Median, and Mode effortlessly. To calculate the Mean (Average) in Excel, use the formula =AVERAGE(range). For an accurate calculation, consider using Excel age calculator for Excel’s built-in date functions combined with simple arithmetic operations.