How to calculate arithmetic mean in excel

When analyzing numerical data, you may often be looking for some way to get the "typical" value. For this purpose, you can use the so-called measures of central tendency that represent a single value identifying the central position within a data set or, more technically, the middle or center in a statistical distribution. Sometimes, they are also classified as summary statistics.

The three main measures of central tendency are Mean, Median and Mode. They all are valid measures of central location, but each gives a different indication of a typical value, and under different circumstances some measures are more appropriate to use than others.

How to calculate mean in Excel

Arithmetic mean, also referred to as average, is probably the measure you are most familiar with. The mean is calculated by adding up a group of numbers and then dividing the sum by the count of those numbers.

For example, to calculate the mean of numbers {1, 2, 2, 3, 4, 6}, you add them up, and then divide the sum by 6, which yields 3: (1+2+2+3+4+6)/6=3.

In Microsoft Excel, the mean can be calculated by using one of the following functions:

• AVERAGE- returns an average of numbers.
• AVERAGEA - returns an average of cells with any data (numbers, Boolean and text values).
• AVERAGEIF - finds an average of numbers based on a single criterion.
• AVERAGEIFS - finds an average of numbers based on multiple criteria.

For the in-depth tutorials, please follow the above links. To get a conceptual idea of how these functions work, consider the following example.

In a sales report (please see the screenshot below), supposing you want to get the average of values in cells C2:C8. For this, use this simple formula:

=AVERAGE(C2:C8)

To get the average of only "Banana" sales, use an AVERAGEIF formula:

=AVERAGEIF(A2:A8, "Banana", C2:C8)

To calculate the mean based on 2 conditions, say, the average of "Banana" sales with the status "Delivered", use AVERAGEIFS:

=AVERAGEIFS(C2:C8,A2:A8, "Banana", B2:B8, "Delivered")

You can also enter your conditions in separate cells, and reference those cells in your formulas, like this:

Median is the middle value in a group of numbers, which are arranged in ascending or descending order, i.e. half the numbers are greater than the median and half the numbers are less than the median. For example, the median of the data set {1, 2, 2, 3, 4, 6, 9} is 3.

This works fine when there are an odd number of values in the group. But what if you have an even number of values? In this case, the median is the arithmetic mean (average) of the two middle values. For example, the median of {1, 2, 2, 3, 4, 6} is 2.5. To calculate it, you take the 3rd and 4th values in the data set and average them to get a median of 2.5.

In Microsoft Excel, a median is calculated by using the MEDIAN function. For example, to get the median of all amounts in our sales report, use this formula:

=MEDIAN(C2:C8)

To make the example more illustrative, I've sorted the numbers in column C in ascending order (though it is not actually required for the Excel Median formula to work):

In contrast to average, Microsoft Excel does not provide any special function to calculate median with one or more conditions. However, you can "emulate" the functionality of MEDIANIF and MEDIANIFS by using a combination of two or more functions like shown in these examples:

How to calculate mode in Excel

Mode is the most frequently occurring value in the dataset. While the mean and median require some calculations, a mode value can be found simply by counting the number of times each value occurs.

For example, the mode of the set of values {1, 2, 2, 3, 4, 6} is 2. In Microsoft Excel, you can calculate a mode by using the function of the same name, the MODE function. For our sample data set, the formula goes as follows:

=MODE(C2:C8)

In situations when there are two or more modes in your data set, the Excel MODE function will return the lowest mode.

Generally, there is no "best" measure of central tendency. Which measure to use mostly depends on the type of data you are working with as well as your understanding of the "typical value" you are attempting to estimate.

For a symmetrical distribution (in which values occur at regular frequencies), the mean, median and mode are the same. For a skewed distribution (where there are a small number of extremely high or low values), the three measures of central tendency may be different.

Since the mean is greatly affected by skewed data and outliers (non-typical values that are significantly different from the rest of the data), median is the preferred measure of central tendency for an asymmetrical distribution.

For instance, it is generally accepted that the median is better than the mean for calculating a typical salary. Why? The best way to understand this would be from an example. Please have a look at a few sample salaries for common jobs:

• Electrician - $20/hour
• Nurse - $26/hour
• Police officer - $47/hour
• Sales manager - $54/hour
• Manufacturing engineer - $63/hour

Now, let's calculate the average (mean): add up the above numbers and divide by 5: (20+26+47+54+63)/5=42. So, the average wage is $42/hour. The median wage is $47/hour, and it is the police officer who earns it (1/2 wages are lower, and 1/2 are higher). Well, in this particular case the mean and median give similar numbers.

But let's see what happens if we extend the list of wages by including a celebrity who earns, say, about $30 million/year, which is roughly $14,500/hour. Now, the average wage becomes $2,451.67/hour, a wage that no one earns! By contrast, the median is not significantly changed by this one outlier, it is $50.50/hour.

Agree, the median gives a better idea of what people typically earn because it is not so strongly affected by abnormal salaries.

This is how you calculate mean, median and mode in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

Arithmetic mean, commonly used in statistics, is the average of the numerical values set and is calculated by first calculating the sum of numbers in the set and then dividing the resultant by the count of those numbers.

The formula is as below:

Arithmetic Mean = x1 + x2 + x3 +……+ xn / n

You are free to use this image on your website, templates, etc, Please provide us with an attribution link

Article Link to be HyperlinkedFor eg:

Source: Arithmetic Mean (wallstreetmojo.com)

Where,

• x1, x2, x3, xn are the observations
• n is the number of observations

Alternatively, one can symbolically write it as shown below:

In the above equation, the symbol ∑ known as sigma. It implies the summation of the values.

• Step 1: Calculate the sum of all the observations.

• Arithmetic Mean = x1+x2+x3+…………….+xn/n
• Alternatively, the arithmetic mean formula in symbolic terms is as below:

Examples

Example #1

There are five observations. These are 56, 44, 20, 50, 80. Find their arithmetic mean.

Solution

• Here, the observations are 56, 44, 20, 50, 80.
• n = 5

Therefore, the calculation is as follows,

Example #2

Franklin Inc. is a manufacturing concern with ten workers. There are negotiations between the management of Franklin Inc. and its trade union regarding wages. For this purpose, the CEO of Franklin Inc. wants to calculate the company’s arithmetic mean of workers’ salaries. The following table gives the wages along with the names of the workers.

Calculate the arithmetic mean of wages for the CEO.

Solution

Therefore, the calculation is as follows:

• =(100+120+250+90+110+40+50+150+70+100+10)/10

Example #3

The school principal calls two teachers to his office – one teaches Division A, and the other teaches Division B. Both of them claim that their teaching methods are superior. The principal decides that the division, which has a higher arithmetic mean of marks, will have a better teacher. These are the marks of 7 students each studying in the two divisions.

Find out which division has higher arithmetic mean.

Solution

Division A

The calculation is as follows:

• =(56+60+56+64+70+55+50)/7

Division B

The calculation is as follows:

• =(70+65+60+65+75+55+65)/7

The arithmetic mean of Division A is 58.71 marks, and for Division B is 65 marks (higher)

Arithmetic Mean in Excel

There is company Grandsoft Inc. which is listed on the stock exchangesStock exchange refers to a market that facilitates the buying and selling of listed securities such as public company stocks, exchange-traded funds, debt instruments, options, etc., as per the standard regulations and guidelines—for instance, NYSE and NASDAQ.read more. Different analysts have given their target price of the stockPrice Target in the context of stock markets, means the expected valuation of a stock in the coming future and the valuation may be done either by the stock analysts or by the investors themselves. For an investor, price target reflects the price at which he will be willing to buy or sell the stock at a particular period of time or mark an exit from their current position.read more. Calculate the arithmetic mean of the stock prices.

Solution

In Excel, there is an in-built formula to calculate the mean.

Step #1 – Select a blank cell and type =AVERAGE(B2:B8)

Step #2 – Press “Enter” to get the answer

Relevance and Uses

The arithmetic mean is one of the most important statistics and is most commonly used as the most popular measure of central tendencyCentral Tendency is a statistical measure that displays the centre point of the entire Data Distribution & you can find it using 3 different measures, i.e., Mean, Median, & Mode.read more. It is straightforward to calculate and does not require knowledge of high-end statisticsStatistics is the science behind identifying, collecting, organizing and summarizing, analyzing, interpreting, and finally, presenting such data, either qualitative or quantitative, which helps make better and effective decisions with relevance.read more. One may use it when all observations in the data set are equally important. A weighted mean is used if some statements are more important than others.

Recommended Articles

This article is a guide to Arithmetic Mean Formula. Here, we discuss the arithmetic mean calculation using its formula, practical examples, and a downloadable Excel template. You can learn more about Excel modeling from the following articles: –