The AVERAGEA function in Excel is used to calculate the average (arithmetic mean) of a set of values, where it considers both numbers and text within the cell range. The AVERAGEA function treats text as 0, logical values as TRUE = 1 and FALSE = 0, and ignores empty cells. Here’s how to use it:
Syntax
AVERAGEA(value1, [value2], ...)
- value1: This is required. The first value or cell reference or range for which you want the average.
- value2, …: These are optional. Additional values, cell references, or ranges for which you want the average.
Steps to Use
- Select the Cell: Click on the cell where you want the result of the AVERAGEA function to appear.
- Enter the Function:
- Type the formula `=AVERAGEA(` followed by the range of cells you want to include.
- For example, if you want to calculate the average of cells A1 to A5, you would enter `=AVERAGEA(A1:A5)`.
- Close the Parenthesis and Hit Enter: After specifying the range, close the parenthesis and press Enter. The cell will display the average of the specified range considering the criteria explained above.
Example
Suppose you have the following values in cells A1 to A5:
- A1: 5
- A2: 10
- A3: “Text” (or any non-numeric value)
- A4: TRUE
- A5: 15
Using `=AVERAGEA(A1:A5)` will calculate as follows:
- Total numerical value calculation: 5 (A1) + 10 (A2) + 0 (A3, text as 0) + 1 (A4, TRUE as 1) + 15 (A5) = 31
- Number of values: 5 (A1, A2, A3, A4, A5)
- So, Average = 31/5 = 6.2
Notes
- Remember, if your dataset includes logical values or text that represent numeric data, AVERAGEA can be very useful because it includes these in the calculation, as opposed to AVERAGE which only computes numbers.
- If you want to include calculations purely based on numbers, you should use the standard `AVERAGE` function instead.
Unlock Your Potential
Excel
Access
Power BI
