Calculate the Population Mean in Excel

In Excel, you can use various methods to calculate the population mean or the average of all the values in an entire population. The population is the complete set of all possible observations, for example, the prices of all tablet computers in a store. 

All the methods for calculating the population mean in Excel compute the population mean by dividing the sum of all the values in the population by their count or number. 

You can calculate the population mean to gain valuable insights for your business. For example, knowing the average price of a specific product in your store can help you set competitive prices. If the average price is higher or lower than that of your competitors, you can adjust your pricing strategy to attract more customers or increase profit margins.

Note: The population mean differs from the sample mean in that when it is impossible or impractical to measure the entire population, you can calculate the sample mean, which is the average of all values in a subset of the population.

I will show you various ways to calculate the population mean in Excel. 

Method #1: Calculate the Population Mean in Excel Using Excel’s Status Bar

You can quickly calculate the population mean in Excel without formulas by checking the status bar at the bottom right of the Excel window.

Suppose you have the below price list of all the tablet computers in a particular store and want to calculate the population mean, the average price of the tablets.

Here’s how to calculate the average price of the tablets using Excel’s status bar:

1. Select the cell range B2:B11, containing the prices you want to average.

2. Look at the status bar at the bottom right of the Excel window. The status displays various quick calculations including the average.

The population mean, or the average price of the tablets is $587.

3. Type the average on the status in cell B12.

Note: If you don’t see ‘Average’ on the status bar, right-click the status bar and ensure that ‘Average’ is checked on the list of available options.

Method #2: Calculate the Population Mean in Excel Using the Quick Analysis Feature

You can compute the population mean of a specific dataset using Excel’s Quick Analysis feature.

The Quick Analysis feature lets you quickly apply common data analysis options to a selected range of cells, such as performing basic calculations like finding the population mean or average of specific values.

Suppose you have the below price list of all the tablet computers in a particular store and want to calculate the population mean, the average price of the tablets.

Here’s how to calculate the population mean, or the average price of the tablets using the Quick Analysis feature:

1. Select the cell range B2:B11 that contains the prices you want to average and notice the Quick Analysis icon in the bottom right corner of the selection.

2. Click the Quick Analysis icon to open a menu of options. Open the Totals menu and click the Average option.

The above step enters the average of the selected prices in cell B12, as shown below.

Also read: Calculate Mean Squared Error (MSE) in Excel?

Method #3: Calculate the Population Mean in Excel Using the Table Total Row

You can convert a data range to an Excel table and use its total row to calculate the population mean.

Suppose you have the below price list of all the tablet computers in a particular store and want to calculate the population mean, the average price of the tablets.

You can use the steps below to calculate the average price of the tablets using an Excel table’s total row.

Step #1: Convert the Data Range to an Excel Table

Select any cell in the data range and press CTRL + T.

Excel automatically detects the dimensions of your data range and identifies if it has a header, then displays the Create Table dialog box.

You can manually adjust the data range if it’s incorrect.

Click OK on the dialog box.

Excel converts the data range to an Excel table, as shown below.

Step #2: Enable the Table’s Total Row 

To Enable the Total Row in your Excel table, click any cell inside the table, go to the Table Design tab, and check the Total Row option in the Table Style Options group.

The above step enables the Total Row in the table, as shown below.

Step #3: Select the Average Option in the Total Row

Select cell B12  and notice the drop-down arrow next to it.

Click the drop-down arrow and select the ‘Average’ option on the list.

The above step enters the average price in cell B12. Change the row header from ‘Total’ to ‘Average’ as shown below.

Method #4: Calculate the Population Mean in Excel Using the AVERAGE Function

Using the AVERAGE function, you can calculate the population mean of a specific dataset in Excel. The AVERAGE function returns the population mean or the average of the arguments you supply.

Suppose you have the below price list of all the tablet computers in a particular store and want to calculate the population mean, the average price of the tablets.

Here’s how to calculate the population mean, or the average price of the tablets using the AVERAGE function:

1. Select cell B12.
2. Type in the formula below and press Enter.

=AVERAGE(B2:B11)

The formula returns 587, the population mean or the average price of all the tablet computers in the store.

Method #5: Calculate the Population Mean in Excel Using the AVERAGEIF Function

You can use the AVERAGEIF function to calculate the population mean or the average of all the cells in a cell range that meet your specified criteria.

Suppose you have the below price list of all the tablet and desktop computers in a particular store and want to calculate the population mean, or the average price, for the tablets only.

Here’s how you can calculate the average price for the tablets only using the AVERAGEIF function:

1. Select cell C17, type in the formula below, and press Enter.

=AVERAGEIF(B2:B16,"Tablet",C2:C16)

The formula returns 587, the population mean, or the average price of all the tablet computers in the store.

The AVERAGEIF function calculates the average price for the cell range C2:C16, but only for rows where the corresponding value in the cell range B2:B16 is ‘Tablet.’

Also read: Weighted Average Formula In Excel

Method #6: Calculate the Population Mean in Excel Using an Array Formula With the SUM Function

If your dataset contains multiple columns you want to include in calculating the population mean or average, you can use an array formula with the SUM function.

Suppose you have the dataset below showing the quantities and prices of tablet computers in a store and want to calculate the average for the tablets.

Here’s how you can do it using an array formula with the SUM function:

1. Select cell C12, type in the formula below, and press Enter. If you have an older version of Excel, press CTRL + Shift + Enter.

=INT(SUM(B2:B11 * C2:C11) / SUM(B2:B11))

The formula returns 472, the weighted average price rounded down to the nearest integer.

Method #7: Calculate the Population Mean in Excel Using the AutoSum Feature

You can use the ‘Average’ option on Excel’s AutoSum feature to calculate the average of a range of cells.

Suppose you have the dataset below showing the prices of all the tablet computers in a particular store and want to calculate the average price for the tablets.

You can use the steps below to do it:

1. Select cell B12.
2. Click the Home tab, click the down arrow next to the AutoSum icon on the Editing group to open the drop-down list, and select the Average option.

Excel automatically selects the range it thinks you want to average as shown below.

If the selected range is incorrect, you can adjust it as needed.

3. Press Enter.

The above step returns the average price of the tablets, $587.

Note: You can also find the ‘Average’ option on the AutoSum feature, located in the Function Library group under the Formulas tab. 

Method #8: Calculate the Population Mean in Excel Using the  SUBTOTAL Function

You can calculate the population mean in Excel using a SUBTOTAL formula. The SUBTOTAL function calculates a variety of aggregate values such as averages while allowing you to ignore hidden rows or filtered data if needed.

Assume you have the dataset below showing the prices of all the desktop and tablet computers in a particular store and want to calculate the average price for the tablets only.

You can use the steps below to calculate the average price of the tablets only using the SUBTOTAL function:

Step #1: Filter Out Data for Desktop Computers

Use the steps below to filter out the data for desktop computers:

1. Select any cell in the dataset.
2. Open the Data tab and click the Filter icon on the Sort & Filter group.

Small drop-down arrows appear in the header of each column.

3. Click the drop-down arrow on the Category column you want to filter and deselect the ‘(Select All)’ option on the drop-down list.

4. Select the ‘Tablet’ option on the drop-down list and click OK.

The above step filters out data for desktop computers to only show tablet data, as shown below.

Step #2: Use the SUBTOTAL Function

Select cell C17, type in the formula below, and press Enter.

=SUBTOTAL(101,C2:C15)

The above step returns the average price of the tablets, $587.

The SUBTOTAL function computes the average of the range C2:C15, excluding the filtered-out rows as dictated by the first ‘101’ argument.

Note: When you select the cell containing the formula, Excel displays a yellow triangle beside it. If you hover over the triangle, Excel displays the warning message, ‘The formula in this cell refers to a range that has additional numbers adjacent to it.’ The message indicates that the cell range C2:C15 does not include the values in the filtered-out rows.

Also read: Standard Deviation in Pivot Tables in Excel

Method #9: Calculate the Population Mean in Excel Using the AGGREGATE Function

You can calculate the population mean in Excel using an AGGREGATE formula.

The AGGREGATE function calculates various aggregate values such as averages with the option to ignore specific data types, hidden rows, and errors. It’s a more powerful alternative to the SUBTOTAL function when you need more flexibility in ignoring data.

Assume you have the dataset below showing the prices of all the desktop and tablet computers in a particular store and want to calculate the average price for the tablets only.

You can use the steps below to calculate the average price of the tablets only using the AGGREGATE function:

1. Filter out Desktops’ data as explained in Step #1 of Method #5 above.
2. Select cell C17, type in the formula below, and press Enter.

=AGGREGATE(1,5,C2:C15)

The above step returns the average price of the tablets, $587.

The AGGREGATE function computes the average of the cell range C2:C15, as indicated by the first ‘1’ argument while ignoring the hidden rows within that range as dictated by the second ‘5’ argument.

Also read: Calculate MEDIAN IF in Excel

Method #10: Calculate the Population Mean in Excel Using the Analysis ToolPak Add-in

Excel has the Analysis ToolPak add-in, which you can use to compute the population mean of a dataset. However, this add-in is disabled by default, so you must turn it on before using it.

Turn On the Analysis ToolPak Add-in

You can use the steps below to turn on the Analysis ToolPak Add-in:

1. Click the File button to open the Excel Backstage view.

2. Select the Options category at the bottom of the left sidebar of the Backstage view.

The above step opens the Excel Options dialog box.

3. On the Excel Options dialog box, click the Add-ins category on the left sidebar, select Excel Add-ins on the Manage drop-down list on the right pane, and click the Go button.

The above step opens the Add-ins dialog box.

4. On the Add-ins dialog box, check the Analysis ToolPak option on the Add-ins available list box and click OK.

The above step adds the Analysis group containing the Data Analysis add-in on the Data tab. 

How to Calculate Population Mean Using the Data Analysis ToolPak

Suppose you have the below price list of all the tablet computers in a particular store and want to calculate the population mean or the average price of the tablets.

Here’s how to do it using the Data Analysis ToolPak:

1. Open the Data tab and click the Data Analysis ToolPak option on the Analysis group.

The above step opens the Data Analysis dialog box.

2. On the Data Analysis dialog box, select Descriptive Statistics on the Analysis Tools list box and click OK.

The above step opens the Descriptive Statistics dialog box.

3. Do the following on the Descriptive Statistics dialog box:

1. On the Input options group, click the range selector button on the Input Range box and select the cell range B2:B11containing the prices you want to average.
2. On the Output options group, choose where you want the summary statistics displayed. You can select the worksheet containing the data, a new worksheet in the current workbook, or a worksheet in a new workbook. In this example, I have chosen the cell range beginning at cell E2 on the worksheet with the dataset.
3. Select the ‘Summary Statistics’ option.
4. Click OK.

The above steps output a summary of descriptive statistics to the target range. The first statistic on the list is the population mean or the average price of tablet computers in the store.

Also read: How to Calculate Covariance in Excel?

Method #11: Calculate the Population Mean in Excel Using a PivotTable

You can use a Pivot Table to calculate the population mean or the average of a dataset in Excel. A Pivot Table is a feature that lets you quickly summarize and present large amounts of data. 

Suppose you have the below price list of all the tablet computers in a particular store and want to calculate the population mean or the average price of the tablets.

Here’s how to do it using a PivotTable:

1. Convert the data range to an Excel table by selecting any cell and pressing CTRL + T.
2. Select any cell in the Excel table.
3. Click the Insert tab, open the PivotTable drop-down list on the Tables group, and select the ‘From Table/Range’ option.

The above step opens the ‘PivotTable from table or range’ dialog box.

4. On the ‘PivotTable from table or range’ dialog box, ensure that Excel has selected the correct table or range, and choose where you want to place the PivotTable. In this example, I have chosen a location on the existing worksheet.

5. Click OK.

The above step places an empty PivotTable on the worksheet and displays the PivotTable Fields task pane on the right of the Excel window.

6. On the PivotTable Fields task pane, drag the ‘Tablet’ field to the Rows area and the ‘Price (USD)’ field to the Values area.

The above step populates the empty PivotTable on the worksheet with values from the ‘Tablet’ and ‘Price (USD)’ fields.

7. Right-click the Pivot Table, hover over the ‘Summarize Values By’ option on the shortcut menu, and click ‘Average’ on the submenu.

The PivotTable shows the population mean, or the average price of the table computers on the Grand Total row.

Method #12: Calculate the Population Mean in Excel Using Power Query

You can use Power Query to calculate the population mean or the average of a dataset in Excel. Power Query in Excel is a feature that lets you import, clean, transform, and reshape data from various sources into Excel. 

Suppose you have the below price list of all the tablet computers in a particular store and want to calculate the population mean or the average price of the tablets.

Here’s how to do it using Power Query:

1. Convert the data range to an Excel table by selecting any cell in the range and pressing CTRL + T.
2. Select any cell in the Excel table.
3. Click the Data tab, open the Get Data drop-down menu, hover over the ‘From Other Sources’ option, and select ‘From Table/Range’ on the submenu.

The above step opens the Power Query Editor with data from the Excel table.

4. On the Power Query Editor, select the ‘Price (USD)’ column, click the ‘Transform’ tab, open the ‘Statistics’ drop-down list, and select the ‘Average’ option.

The above step calculates the population mean or the average price of the tablets and displays it in the Power Query Editor.

5. Click the Home tab, open the Close & Load drop-down list on the Close group, and select the Close & Load option.

The above step loads the average value onto a new worksheet.

In this article, I have shown you several ways to calculate the population mean in Excel. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• Calculate Correlation Coefficient in Excel
• BETWEEN Formula in Excel
• How to Calculate Class Width in Excel
• How to Find Z-score in Excel?