Spreadsheet.com uses the AVERAGE function to calculate the mean of a set of values. Get your own copy of the 🔗template used in this tutorial to follow along.
Contents
Purpose
The AVERAGE function returns the average value of a series of numbers, cells, or both.
Syntax
=AVERAGE (value1, value2, …) or =AVERAGE ([cell/range])
value1– The first number or range to consider while calculating the average value.value2, …– [OPTIONAL] Additional numbers to consider.
Video Explanation
Examples
Example 1 – Average Function When all Values are Numeric
The above dataset’s price (column E) is calculated using the formula: =AVERAGE(E10:E29). The formula computes the average of the values from E10 through E29. If any blank or non-numeric values were in the specified range, they would be ignored and not factored into the calculation.
Example 2 – Average When Some Values are not Numeric
The average value on cell G31 has been calculated based on the values from G10 through G29. However, as we said, AVERAGE only considers values that are not null and are numerical. Consequently, the calculation left out cells G23 and G27. Why? G23 is a null value, meaning it’s blank, while G27 is a Boolean value.
It’s also worth noting that spreadsheet.com used G11, G17, and G20 in computing the average. A numerical value of 0 is different than a blank cell. Zero is treated as a number and is counted by the AVERAGE function, while an empty value is not.
What if we defined a range that did not contain any numerical values? What would be the output in such a scenario?
Example 3 – Average Function When all Values are not Numeric
As you can see, all values in the range H10:H29 are boolean as spreadsheet.com treats checkboxes as treated as boolean values. Therefore, the formula ignored all the values. Ignoring the values means that the sum of values is zero divided by the number of values which is also zero. Mathematically, 0/0 is undefined, hence the reason we are getting a #DIV/0 error. To work with non-numeric values in the calculation of the average, use the AVERAGEA function instead.
Example 4 – AVERAGE Function with Numbers and Text
Here we have two columns of data. One column has only numbers, and the other column has numbers and text. In the column on the left, the average is as expected. On the right, however, the AVERAGE function only uses the cells with numerical values. Therefore, AVERAGE is computing the average of only 8 and 9. AVERAGEA, on the other hand, counts text values as 0 and thus is arriving at a lower number by averaging 0, 8, 0, and 9.
Notes
- AVERAGE function only considers numerical values in the calculation.
- The AVERAGE function ignores blank cells and cells containing non-numerical values such as text, boolean, et cetera.
- The AVERAGE function counts cells containing 0s since zero is a real number.
- Consider using the SUBTOTAL function if you have multiple averages in one column.