What Is AVERAGEA Function in Google Sheets?
AVERAGEA in Google Sheets is used to calculate the mean or average of a data set. It may be numbers, text, or logical values like TRUE or FALSE. The AVERAGEA function treats logical values TRUE as 1 and FALSE and text as 0. Thus, unlike AVERAGE, it is useful when you have mixed data types. Thus, you get a more detailed or inclusive average, not just based on the numbers.
For example, we have a set of values in Column A. If we use the following function =AVERAGEA(A1:A5), it will calculate the average of 10, 2, 3, 0 (FALSE), and 1 (Real). We get the result as 3.2, which is 16/5. As explained above, it even considers non-numeric values for the calculation. This makes AVERAGEA different from the AVERAGE function, which ignores non-numeric values.
Key Takeaways
The AVERAGEA calculates the mean of a set of values but is different from AVERAGE. Here, when the inputs are text or Boolean values, the AVERAGEA function includes them in the calculations.
The syntax is as follows:
=AVERAGEA (value1, value2, …)
- value1 – The number or range to consider for average calculation.
- value2, … – [optional] Additional numbers to consider.
The function counts TRUE as 1 and FALSE as 0 and all text as 0. However, it ignores blank cells.
Syntax
The AVERAGEA function in Google Sheets calculates the arithmetic mean of a dataset, by including all data types. It uses the value of zero for non-numeric values (like text or logical values. Its syntax is:
=AVERAGEA(value1, [value2, …]).
Explanation
The values can be individual cells, ranges of cells, or direct values.
- value1: It is the first required argument.
- [value2, …]: The remaining are optional arguments. One can include extra cells, ranges, or values separated by commas for which you want to find the average.
How To Use AVERAGEA Function in Google Sheets?
The AVERAGEA function treats non-numeric values as zero when calculating the average. There are two ways to enter this interesting function in Google Sheets.
- Entering AVERAGEA Manually
- Entering AVERAGEA Through the Menu Bar
We give you a simple guide on how to use AVERAGEA in Google Sheets, in both ways.
Entering AVERAGEA Manually
This method is useful especially if you already know which cells you want to include.
Step 1: Enter a set of values in a new sheet, as shown below.
Step 2: Click on an empty cell where you want the result to appear. Here, we click on A8.
Step 3: Type the formula as shown: =AVERAGEA(A1:A7). Here, we have specified a range.
Step 4: Press Enter. The result will be (10 + 1 + 1 + 0 + 11 + 0 + 7) / 7 = 4.28…
You can also give individual values like =AVERAGEA(5, TRUE, FALSE) and find the average. Here, it is 2.
Entering AVERAGEA Through the Menu Bar
This method is quick as we can directly enter the formula instead of typing it out.
- Click on the cell where you want the average to appear.
- Go to the Insert” menu at the top.
- Choose “Function” -> “Statistical” -> “AVERAGEA.”
- Highlight the range of cells for which you want the average. Press Enter.
Examples
Example #1 – Calculate the average feedback score, including text responses like “N/A” as 0
In this example, we have the feedback and ratings given by some customers for a restaurant. We wish to calculate the average feedback score. We have to treat text like “N/A” or “Nice” as 0. One can use the AVERAGEA function instead of AVERAGE in this scenario. Let us look at how to do the same.
Step 1: Enter all the values in a sheet as shown below.
Step 2: Click on the cell where you want the result. Here, we choose B2. Enter the formula as follows:
=AVERAGEA(A2:A8)
Step 3: Press Enter. You get the average customer feedback.
To cross-verify, find the sum of the values from A2:A8 and divide by the number of elements, 7.
Explanation:
Here, AVERAGEA in Google Sheets converts the feedback to:
Total = 14
Number of items = 7
Average = 14 ÷ 7 = 2
Example #2 – Compute average weekly sales, treating “No Sale” text entries as zero sales
We have a store that is documenting its weekly sales. The store wishes to gauge its performance by finding the average weekly sales. Let us look at how to do the same using AVERAGEA in Google Sheets. We use this function instead of AVERAGE because, on weeks when there are no sales, the entry made is “No Sales.” Hence, it should be counted as zero sales.
Step 1: Enter all the details in a sheet for calculations.
Step 2: Click on a blank cell where you want the average to appear (e.g., B8). Type the following formula:
=AVERAGEA(B2:B7)
Step 3: Press Enter to see the result.
Explanation
“No Sales” is treated as 0
So values become: 1200, 950, 0, 1100, 1050, 0
Total = 4300, Number of entries = 6
Average = 4300 ÷ 6 = 717
Now we get the real weekly average.
Example #3 – Determine the average attendance by treating TRUE as present and FALSE/text as absent
In this example, a teacher is tracking the attendance of some of her students for a week. She uses TRUE if a student is present and FALSE or “Absent” if a student missed classes. Let us find the students’ average attendance rate.
We will find the average weekly attendance, treating TRUE = 1 and all others as 0.
Step 1: Enter all the students’ details in a sheet. Here, we have four students.
Step 2: Click on a blank cell where you want the result for the first student to appear. Here, it is B7. Type the formula:
=AVERAGEA(B2:B6)
Step 3: Press Enter.
Step 4: Drag the formula horizontally to E7 to find the average for each student.
Explanation:
Here, TRUE is counted as 1. FALSE, and “Absent” are counted as 0
Let us find the attendance percentage by clicking on the percentage symbol.
Here, we use AVERAGEA because the AVERAGE function would ignore non-numeric values. But AVERAGEA recognizes logical values and includes everything in the count and gives the right percentage.
Important Things to Note
- When we include text in our data, AVERAGEA will not ignore it, unlike the regular AVERAGE functions. Instead, it counts that text as 0 when calculating the average. So, the average may go down just because of some non-numeric values in the cells.
- Booleans are treated as their numerical equivalents in the calculation. It treats TRUE as 1 and FALSE as 0.
- Always be aware that AVERAGEA treats empty cells as zeros. It may cause significant changes in the average calculations if there are many empty cells.
- We use AVERAGEA when there is a chance that our data might include BOOLEAN or text values.
Frequently Asked Questions (FAQs)
AVERAGEA vs AVERAGE
There are quite a few functions in Google Sheets to find various types of averages. The most important ones are AVERAGE and AVERAGEA.
AVERAGE is a function that includes only numbers. It ignores empty cells and text values and calculates the arithmetic mean of only numbers.
AVERAGEA in Google Sheets includes numbers, text, and logical values. It treats TRUE as 1 and FALSE/text as 0). Words are counted as 0, and that may lower the average.
However, blank (empty) cells are ignored by AVERAGEA as well as AVERAGE.
Yes, combining AVERAGEA with other functions is very useful for complex calculations.
FILTER – We can combine AVERAGEA with the FILTER function to find the average of just certain values. For instance,
=AVERAGEA(FILTER(A1:A6, A1:A6<>TRUE, ISNUMBER(A1:A6)))
This formula filters out the cells containing TRUE and only averages the number values.
To find the average of only nonblank cells, we can combine AVERAGEA with IF and ARRAYFORMULA as follows:
=AVERAGEA(ARRAYFORMULA(IF(B1:B5<>””, B1:B5, ))).
1. The numbers are directly included when calculating the average.
2. Text is treated as zero and included in the average calculation.
3. Empty cells are treated as zero and included
4. Boolean values TRUE and FALSE are treated as 1 and 0, respectively.
5. Error values will give errors when used as arguments.
Download Template
This article must help understand AVERAGEA Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is AVERAGEA Function in Google Sheets. We learn its syntax & how to use it to find the average of a dataset with examples. You can learn more from the following articles. –
Leave a Reply