Let’s say you manage a store that sells various electronic products like laptops, smartphones, and headphones. You have recorded the monthly sales for each product over the past year in Google Sheets.
Now, you want to calculate the average sales for each product to understand which ones are performing well.
In Google Sheets, there are several ways to average values, and in this tutorial, we will discuss each one. Then, you can choose which method is perfect for your situation.
AVERAGE Function
The AVERAGE Function is a basic but one of the most used statistical functions that calculates the average of given numbers. AVERAGE has a simple syntax =AVERAGE(value1, [value2, …]) where you can directly refer to a range or input values into the function.
- Enter the “=” operator.
- Type the function name and opening parentheses.
- Refer to the range by selecting it.
- Close the function and hit enter.
Now, this formula finds the average numbers in cells B2 through M2. These cells contain monthly laptop sales data from January to December. Once you enter the formula, you can drag it to the below cells to get the average for all the products.
When you use the AVERAGE with values in the range that include errors (like #DIV/0! or #VALUE!), the function will return an error instead of calculating the average. This is because any error within the range disrupts the calculation.
When numbers are saved as text, the AVERAGE function will ignore those text values and calculate the average of the actual numeric values. But if all values in the range are text, it will return a #DIV/0! Error because there are no numeric values to average.
AVERAGE with SUM and COUNT
You can also use a combination of SUM and COUNT functions, where you need to sum the values first and divide them by the number of values. In the example below, we used the same data and a formula combining SUM and COUNT.
=SUM(B2:M2)/COUNT(B2:M2)
The formula adds up all the numbers in cells B2 to M2 and then divides the total by the number of those cells. This gives you the average of the numbers in that range.
This formula is useful instead of the AVERAGE function when handling specific situations, such as ignoring errors or text values in your range. By using =SUM(B2:M2)/COUNT(B2:M2), you ensure that only numeric values are included in the calculation.
Average from the Status Bar
For a quick calculation and when you don’t need to write a formula, you can use the status bar to get the average numbers from the selected range.
First, select the cells for which you want to show the average. For example, highlight cells B2 to M2. You have the status bar at the bottom right of your window. It automatically shows quick calculations like Sum, Average, and Count.
Click on it if it doesn’t show the average. It will open an option menu, and you can select the average from there to calculate the average for the selected cells.
Using AVERAGEIF and AVERAGEIFS to Average with Condition
Apart from the average methods, you have advanced functions to average values based on one or more conditions. These functions can help you perform data analysis better.
AVERAGEIF
In this syntax, the range is the columns/rows where you want to check for the condition, the criteria are the condition to meet (e.g., “>70”), and the optional average_range is the actual cells to average if they are different from the range. If not specified, it averages the cells in the range.
Let’s understand it with an example: suppose your scores are in cells B2 to B10. You can use the AVERAGEIF function like this: =AVERAGEIF(B2:B10, “>70”).
This formula tells Google Sheets to examine the range A2 to A10 and average only scores greater than 70. AVERAGEIF ignores errors or text values in the range when calculating the average. It only considers numbers that meet the criteria. And If no cell meets the criteria, it returns a #DIV/0! Error. Use IFERROR to handle this: =IFERROR(AVERAGEIF(A1:A10, “>70”), 0)
Note – You can use wildcard characters in AVERAGEIF.
AVERAGEIFS
In the same way, AVERAGEIFS allows you to have more than one condition to test and then average the values from a range. AVERAGEIFS has a simple syntax that allows you to specify multiple criteria.
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
- average_range: The range of cells to calculate the average.
- criteria_range1: The range that needs to be tested with criterion1.
- criterion1: The condition to apply to criteria_range1.
- criteria_range2, criterion2, …: Additional ranges and criteria (optional).
Make sure all criteria ranges are the same size as the average range. Mismatched ranges will result in an error. In the example below, we have the same kind of data: you need to average values where the score is below 70 and above 40.
This formula will look at the range B2 to B10 and average only the scores greater than 40 but less than 70. It’s like telling function, “Hey, just focus on the scores in this range that fit between 40 and 70, and give me their average”.
Note – You can use wildcard characters in AVERAGEIFS.
Get the Weighted Average
You can use one more function to get the weighted average in Google Sheets. The name of the function is AVERAGE.WEIGHTED. It calculates the weighted average of a set of values, each with a different weight.
=AVERAGE.WEIGHTED(values, weights, [additional_values], [additional_weights])
- values: The range of cells where you have values you want to average.
- Weights: The range of weights corresponding to the values for calculating the weighted average.
- additional_values, additional_weights: Additional ranges of values and weights (optional).
Now, to understand this function, you can take an example like the one below:
With this formula =AVERAGE.WEIGHTED(A2:A10, B2:B10) will calculate the weighted average of the values in column A, considering the corresponding weights in column B. This means values with higher weights will have a bigger impact on the final average.
If you want to learn more about Google Sheets, make sure to check out these tutorials on (How to Divide in Google Sheets), (How to Multiply in Google Sheets), and (How to Subtract in Google Sheets).