How to calculate mean in excel and why it's essential

By Indeed Editorial Team

Updated 2 December 2022

Published 3 January 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Excel is a spreadsheet program that enables users to efficiently organise, analyse and understand data. Calculating the mean value of a data set is critical for implementing an effective data analysis. By learning to calculate the mean, you might further develop your career as a data analyst and improve your hard skills in Excel and data visualisation. In this article, we discuss the mean in Excel, explain why it's essential to calculate, describe how to do it, learn how to set criteria to calculate the mean, and explore the pros and cons of calculating the mean.

What is mean in Excel?

The mean in Excel is the average number when you add all the data and divide it by the number of data points. Data refers to numbers, facts and statistics collected for reference or analysis. A data point is a set of one or more measures on a single segment of the unit of observation. For example, in a study of the height and weight of children of a given age, a data point might be any of the values you're recording.

Why is it essential to calculate the mean?

Calculating the mean is essential because it refers to the average, the most common factor used to estimate the centre of a numerical data set. The mean is the average value of the data set and dictates the norm value. Adding up numbers in a data set and dividing them by the number of data points gives you the typical value of a particular information set. Below are some helpful ways of using the mean value in data analysis:

  • To find an average value to compare with each data point: The mean enables you to find a reasonable standard value with which to compare each data point. For example, if you know the mean figure for past sales per hour, you can pinpoint the busiest sales periods throughout the day, which helps determine the optimum inventory to meet market requirements.

  • To compare past data: Knowing the mean of any data set is essential in enabling you to compare how your data set changes over a period. This function is useful when tracking sales, measuring productivity and managing performance.

  • To spot trends in sales and identify cycles: A rolling average in Excel may allow you to foresee seasonal variations in sales. With this information, you might be able to make sure you meet staffing requirements and supply of goods.

  • To determine key performance indicators and establish quotas: You might use the mean to measure what an average employee may reasonably achieve. This value can help develop performance expectations and promote self-management among employees.

  • To inform business strategy and guide project management: Means are excellent tools for establishing expectations when predicting and managing budgets, setting schedules, and estimating working hours. This gives businesses a good idea of selecting and using resources.

  • To help build strategies for individual projects: Considering personal allows projects also helps businesses to see their business strategies. This informs broader business decisions.

Related: How to highlight excel skills on your CV

How to calculate the mean in Excel

You can accomplish this task by using the average function. Below are the steps you may follow to complete this simple process:

1. Enter your data manually into the Excel spreadsheet

The first step is to add all the data you want to analyse into the Excel spreadsheet. Enter the data manually by opening the spreadsheet and typing each information element into the cells. Check your entries carefully for errors.

2. Import your information

You may import data from various data sources. Most programs let you export information to a spreadsheet. This is a practical, time-saving option if you have more information to enter by hand. The function allows you to import many data values simultaneously.

3. Set up your spreadsheet

Excel lets you lay out information in whatever way you need. For example, you can begin by typing data into column C following no specific order. Suppose you're importing data from another spreadsheet or program containing the information you don't need. In that case, you can identify which columns have the relevant data required to calculate the mean. You might select the necessary data to calculate and keep the rest for future analysis. If you aren't likely to use the rest of the data, you might delete it.

4. Select an empty cell to enter the average formula

You can calculate the mean on the same spreadsheet you're using for your data analysis. As you want to preserve all the data under analysis, select an empty cell at the bottom of the column containing the information and insert the average formula. Alternatively, choose a blank cell on the right or at the top of the column.

Related: Computer skills: definitions and examples

5. Enter the formula to find the mean

To calculate the mean, you first type in the equal sign and then the word 'average'. Alternatively, you can select the function from the formula drop-down menu. Both ways can calculate the mean result. Here is the formula you can type manually:

=average

6. Select the cells with data

The next step is to select the cells containing the data you may want to include to calculate the mean. For example, to calculate the mean for column A, cells 10 to 20, you type in the equal sign, then the word 'average', followed by an open parenthesis mark, the letter 'A', the number 10, a colon, the letter 'A', the number 20 and finally a close parenthesis mark. Then you can press the enter command to get the mean value. Here is the formula of the example:

=average(A10:A20)

Setting criteria to calculate the mean

Sometimes calculating the mean can be more complex. This may happen when you have cells with non-numeric values or when it's necessary to find the mean and meet specific criteria. Here you can use different formulae to determine which information on the spreadsheet to include in the calculation. The steps below outline formulae to get error-free calculations when handling blank cells and setting criteria:

Handling blank cells or cells with non-numeric values

It's important to leave blank cells out of your criteria, as the calculation may omit them and affect the result. You can avoid this by filling the empty cells with the value of zero. You can also ensure the inclusion of all data by using the following formula:

=averagea

Alternatively, you may include all records, even the blank cells. To do this, you can add the function 'sum', which adds together all the values, and the command 'rows', which includes the number of rows in the range. The formula divides the 'sum' by the number of 'rows', resulting in a mean value for all data points. Here is the formula to calculate the mean for column A, cells 10 to 20:

=sum(A10:A20)/rows(A10:A20)

Handling one or more criteria

To establish the mean value of data for one or more sets of criteria, you may use the formula 'averageif', which helps narrow your calculation for data analysis. For example, you can use it to calculate the mean salary of employees over a specific pay grade or the mean overtime hours of employees within a particular age bracket. Here is the formula you can type manually:

=averageif

What are the advantages and disadvantages of calculating the mean?

To fully understand the concept of calculating the mean, it's helpful to consider its advantages and disadvantages.

Advantages

Here are four benefits of calculating the mean:

  • It's a simple calculation: You can calculate the mean using basic knowledge of addition and division.

  • It accounts for all data points: Excel doesn't exclude any values which guarantee accurate results.

  • It works well for lists of similar numbers: This means it's ideal for numbers that don't have differences.

  • The result gives a finite figure: The result is a clear, precise figure that users can consider for data analysis.

Related: Analytical skills: definitions and examples

Disadvantages

Here are four examples where the mean may not be the most appropriate calculation:

  • If the range of numbers is extreme: This leads to a misleading average value calculation.

  • When the data values are volatile: Unpredictable factors may result in a mean result that is less helpful.

  • With a significant volume of data points: Millions of data points may make the calculation very time-consuming.

  • For ratio and percentage data values: Mean calculations for these data forms are complex.

Please note that none of the organisations mentioned in this article are affiliated with Indeed.

Related:

  • A complete guide to the Excel MOD function for professionals

  • A guide on how to find duplicates in Excel

  • How to alphabetise in Excel by cells, columns and rows

  • 12 basic Excel skills to help you advance your career

  • What are Excel alternatives? (11 best software to use)


Related articles

How to use data consolidation in Excel: a complete guide

Explore more articles