# Google Sheets AVERAGEA Function: Syntax, Examples & Tips - URL: https://betteranalyst.com/google-sheets/averagea-function - Author: Better Analyst Formula Bot is now Better Analyst—same account, more powerful analysis.Formula Bot is now Better Analyst—same account and workflows, with more powerful analysis, dashboards, and automation. [Learn what changed](https://betteranalyst.com/formula-bot-is-now-better-analyst) Returns the mean of a set of values, counting text as 0 and TRUE/FALSE as 1/0. ``` =AVERAGEA(value1, [value2, ...]) ``` Generate FormulaExplain Formula One free generation or explanation per browser, shared across Excel and Google Sheets. Account usage limits apply afterward. Describe the AVERAGEA you need in plain EnglishGenerate Formula ## Parameters | Parameter | Required | Description | | - | - | - | | **value1** | Yes | The first value, cell, or range to include in the average. | | **value2, ...** | No | Additional values, cells, or ranges to include. | ## What Is AVERAGEA? AVERAGEA calculates a mean like [AVERAGE](https://betteranalyst.com/google-sheets/average-function), but it treats non-numeric cells differently. Where AVERAGE skips text and logical values, AVERAGEA **counts them**: text is treated as 0, TRUE as 1, and FALSE as 0. The result is an average over every non-empty cell in the range, not just the numeric ones. ## How AVERAGEA Works For each referenced cell, AVERAGEA decides what it is worth: | Cell contents | Value used by AVERAGEA | Counted in divisor? | | - | - | - | | Number | The number | Yes | | Text (including `"n/a"` or `"pending"`) | 0 | Yes | | TRUE | 1 | Yes | | FALSE | 0 | Yes | | Empty cell | Not used | No | Empty cells are still ignored, just as with AVERAGE. So the key question AVERAGEA answers is: "If every filled-in cell counts, what is the mean?" ## Basic Examples ### A column with some text entries Suppose B2:B6 contains 10, 20, `absent`, 30, and an empty cell. ``` =AVERAGEA(B2:B6) ``` Returns 15: the total of 10 + 20 + 0 + 30 = 60, divided by 4 non-empty cells. `=AVERAGE(B2:B6)` would return 20, because it divides by only the three numbers. ### Share of TRUE values in a checkbox column ``` =AVERAGEA(C2:C101) ``` If C2:C101 is a column of checkboxes, each ticked box is TRUE (1) and each unticked box is FALSE (0). The result is the proportion of boxes that are ticked, for example 0.42 for 42%. ### Mixing numbers and logical values ``` =AVERAGEA(D2:D20) ``` Useful for scoring sheets where some answers are entered as TRUE/FALSE and others as numeric points. ## AVERAGEA vs. AVERAGE | Situation | AVERAGE | AVERAGEA | | - | - | - | | Numbers only | Same result | Same result | | Text entries such as "absent" | Skipped | Counted as 0 | | TRUE / FALSE in cells | Skipped | Counted as 1 / 0 | | Empty cells | Skipped | Skipped | Choose AVERAGEA when a text entry genuinely represents a zero score. Choose AVERAGE when text means "no data" and should not affect the result. ## Common Use Cases - **Attendance or completion rates**: a column of checkboxes averaged into a percentage - **Scoring with placeholders**: entries such as "missed" should count as zero points - **Quality checks**: pass/fail columns stored as TRUE/FALSE - **Survey data**: yes/no responses recorded as logical values ## Common Errors - **Unexpectedly low results**: Header rows or notes inside the range are counted as 0. Make sure the range covers data only. - **Numbers stored as text**: A value such as `"45"` typed as text is counted as 0, not 45. Convert it to a real number first. - **#DIV/0!**: Every referenced cell is empty. - **Error values**: Any error in the range makes the whole formula return that error. ## Tips and Best Practices - Format the result as a percentage when averaging checkboxes; it reads as a completion rate. - If you want to count blanks as zero too, fill them with 0 or use `=SUM(range)/ROWS(range)`. - To compare the two behaviours, place AVERAGE and AVERAGEA side by side. A gap tells you how much text or logical data the range contains. - For conditional averages, use [AVERAGEIF](https://betteranalyst.com/google-sheets/averageif-function); note that it ignores text in the averaged range, like AVERAGE. ## Related Functions - [AVERAGE](https://betteranalyst.com/google-sheets/average-function): ignores text and logical values - [AVERAGEIF](https://betteranalyst.com/google-sheets/averageif-function): averages with a condition - [COUNT](https://betteranalyst.com/google-sheets/count-function): counts numeric cells only - [IF](https://betteranalyst.com/google-sheets/if-function): convert text to numbers before averaging ## Example use case A training coordinator tracks course completion with a checkbox column. =AVERAGEA(C2:C120) returns the share of employees who have finished, because each ticked box counts as 1 and each unticked box as 0. ## Need more help with Google Sheets formulas? Sign up for Better Analyst and get unlimited AI-powered formula generation, explanations, and advanced data analysis. [Create Free Account](https://betteranalyst.com/signup)[Try Formula Generator](https://betteranalyst.com/excel-ai) ## Google Sheets how-to guides - [How To Lock A Row In Google Sheets](https://betteranalyst.com/google-sheets/how-to-lock-a-row-in-google-sheets) - [How To Lock Cells In Google Sheets](https://betteranalyst.com/google-sheets/how-to-lock-cells-in-google-sheets) - [How To Make A Copy Of An Google Sheets Sheet](https://betteranalyst.com/google-sheets/how-to-make-a-copy-of-an-google-sheets-sheet) - [How To Make A Graph In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-graph-in-google-sheets) - [How To Make A Histogram In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-histogram-in-google-sheets) - [How To Make A Line Graph In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-line-graph-in-google-sheets) - [How To Make A Pie Chart In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-pie-chart-in-google-sheets) - [How To Make A Table In Google Sheets](https://betteranalyst.com/google-sheets/how-to-make-a-table-in-google-sheets) ## Explore all Google Sheets formulas Browse our complete library of Google Sheets formula guides Compare your options ## Decide with evidence, not a sales pitch. Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose. 1. [01ChatGPTOpen a balanced comparison prompt in ChatGPT.](https://chatgpt.com/?prompt=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 2. [02ClaudeOpen the same research prompt in Claude.](https://claude.ai/new?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) 3. [03PerplexityResearch the comparison with linked web sources.](https://www.perplexity.ai/search?q=Compare%20Better%20Analyst%20with%20using%20a%20general-purpose%20AI%20assistant%20for%20data%20analysis.%20Evaluate%20spreadsheet%20and%20document%20analysis%2C%20dashboards%2C%20repeatable%20reports%2C%20code%20transparency%2C%20data%20connections%2C%20pricing%2C%20and%20limitations.%20Use%20current%20public%20sources%2C%20distinguish%20verified%20facts%20from%20assumptions%2C%20and%20explain%20which%20types%20of%20users%20each%20option%20fits%20best.) AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.