Skip to content

Three Average Spreadsheet Functions

What do you usually think of when you hear the word “average?”

The most common metric that seems to come to mind is the arithmetic mean. This number is calculated by taking all numerical values in a list and dividing it by the total number of values.

However, did you know that the word “average” can also refer to two other metrics? It can potentially refer to the mode or the median as well.

The median is calculated by finding the middle value in a dataset, and the mode represents the value that occurs most often.

In this article, we will cover which spreadsheet functions you can use to calculate all three types of averages. You will learn how to calculate the mean, the mode, and the median.

Calculate the Arithmetic Mean

The mean is what we commonly think of when considering the word “average.” This is why the spreadsheet function used to calculate the arithmetic mean is simply called the AVERAGE function.

The AVERAGE function may contain several arguments but must have at least one. The argument(s) for the AVERAGE function may be individual numbers or references to cells and ranges that contain numbers.

The AVERAGE function takes the numbers, adds them up, and divides the total by the number of values that were added together.

Take a look at the example below.

The AVERAGE function references the range A1:C3. The total of all the numbers in this range is 27. The number of values that were added together is 9.

If you do the math, 27 divided by 9 is equal to 3. Therefore, the average function returns a value of 3.

Calculate the Mode

The mode is another form of average. The mode is simply the number that occurs the most number of times in a set of values.

To calculate the mode in a spreadsheet, you can use the appropriately named MODE function.

The MODE function may contain several arguments but must have at least one. The argument(s) for the MODE function may be individual numbers or references to cells and ranges that contain numbers.

Take a look at the example below to see how this function is used in a spreadsheet.

The MODE function references the range A1:C3. Among the values in this range, the value of 2 occurs three times which is more than any other number. Therefore, the MODE function returns a value of 2.

Calculate the Median

The median is another form of average. The median is simply the middle number in a set of values. For example, take the following set of numbers.

  • 4
  • 2
  • 6

The median among this set of values is 4. The number 2 is the smallest value, and the number 6 is the largest value. This leaves the number 4 right in the middle which is why it is considered the median.

To calculate the median in a spreadsheet, you can use the appropriately named MEDIAN function.

The MEDIAN function may contain several arguments but must have at least one. The argument(s) for the MEDIAN function may be individual numbers or references to cells and ranges that contain numbers.

Take a look at the example below to see how this function is used in a spreadsheet.

The MEDIAN function references the range A1:C3. Among the values in this range, the value of 3 is the middle value. Therefore, the MEDIAN function returns a value of 3.

What Happens If Two Values Are In the Middle?

If your data set contains an even amount of numbers and you try to use the MEDIAN function, what happens? There will be two numbers in the middle, so how is the median calculated?

In the case that there are two numbers in the middle, these two numbers are simply added together and divided by two. The result of this simple calculation is then referred to as the median.

Take a look at the example below.

The two numbers in the middle happen to be 4 and 6. These two numbers are added together and then divided by 2. This results in a final value of 5 which is returned by the MEDIAN function.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top