AVERAGEA Excel

What Is AVERAGEA Excel Function?

The AVERAGEA Excel function calculates the average of numbers in a range, including both numeric and non-numeric values like text or logical values. It treats text as 0 and logical values as 1, making it helpful in analyzing mixed data types accurately. This function is beneficial for datasets with errors or inconsistencies, allowing for a more precise representation of the data and greater flexibility in calculating averages

The example shows how to calculate the average of numbers in a range, including both numeric and non-numeric values like text or logical values using the AVERAGEA function in Excel.

AVERAGEA-Excel-Definition

Enter the formula =AVERAGEA(A2:A4) in cell B2. You get the result as 370, which is the average of the three numbers.

AVERAGEA-Excel-Definition1
Key Takeaways
  • The AVERAGEA Excel function calculates the average of a range of numbers, including non-numeric values like text or logical values. It treats text as 0 and logical values TRUE as 1, making it useful for analysing mixed data accurately. 
  • The syntax of the AVERAGEA function; =AVERAGEA (value).
  • The arguments in a formula can be various types of data, including numbers, names, arrays, references, text representations of numbers, or logical values like TRUE and FALSE.
  • The formula will give errors if the arguments are error values or text that can’t be converted into numbers.

Explanation

The AVERAGEA Excel function includes all cells in the calculation, treating text as a numerical zero. This feature makes it ideal when dealing with datasets that may contain mixed data types. It is beneficial when working with survey responses or financial data that may include both numeric values and text labels. The AVERAGE function automatically excludes empty cells from its calculations, ensuring that only relevant data points are considered.

Syntax

Syntax

Value 1 – This is the mandatory argument. It can be numbers, ranges, named ranges, or cell references in excel that contain values. A maximum of 255 arguments can be supplied.

How To Use AVERAGEA Function in Excel?

To effectively utilize the AVERAGEA function in Excel, follow these steps.

#1 – Access from the Excel ribbon

  1. Choose the cell where you want the answer to appear. Go to the Formulas tab and click on it.

    Step 1(1)

  2. Select More Functions from the menu.

    Step 1(2)

  3. To access statistical functions, select “Statistical” from the drop-down list, then choose “AVERAGEA” from the menu.

    Step 1(3)

  4. Enter the values for the arguments in the Function Arguments window and click OK to continue.

    Step 1(4)

#2 – Enter the worksheet manually

Step 1: To calculate the average deviation in Excel, select an empty cell and type in the formula =AVERAGEA(). Alternatively, start typing =A and then double-click on the AVERAGEA function from the Excel suggestions.

Step-2(1)

Step 2: Press the Enter key to view the result.

Step-2(2)

Examples

Example #1 – Calculate Blank Cells

To understand the AVERAGEA Excel function in calculating the average in the presence of blank cells, let us explore the following example. Below, we have the attendance of some students for five classes per day; we are calculating the average number of classes attended by the students. Take a look at the values in the table provided.

AVERAGEA-Excel-Example-1

Let us now proceed with the following steps.

Step 1: Select cell D2 and enter the AVERAGE formula as follows. Here, we are calculating the average of the values in cells C2 to C6. Press Enter.


=AVERAGEA(C2:C6)

Example-1-Step-1

Step 2: The resulting value will then be displayed in cell D2.

Example-1-Step-2

Explanation: Here, the blank cells are skipped. It means that the AVERAGEA function includes only the numerical cells in its calculation, ignoring the rest.

Hence, we have (2+4+3)/3 = 3.

Sometimes, the presence of blank cells can skew the calculated average, potentially leading to inaccurate results.

Example #2 – Calculate Text

In the example below, we will demonstrate how to calculate the average for the below text values using the AVERAGEA Excel function.

AVERAGEA-Excel-Example-2

Look at the table above. To utilize the AVERAGEA Excel function, please follow the steps outlined below:

Step 1: In cell B2, enter the following formula.

=AVERAGEA(A2:A6)

Example-2-Step-1

Step 2: The resulting value 0 is displayed in cell B2, as shown in the image below.

While utilizing the AVERAGEA Excel function, the text values within a range of cells are automatically ignored and do not factor into the calculation of the average. The function only considers numerical values in the selected range, discarding any text entries to provide an accurate average of the numeric data present. This is why the example shows the result as 0. 

Example-2-Step-2

Example #3 – Calculate Numbers and Text

In this example, the AVERAGEA function is used to calculate the average of the numbers and text. This function is essential for calculating the average of a range of numbers, including text representations of numbers. 

AVERAGEA-Excel-Example-3

To use the AVERAGEA Excel function, just follow these simple steps:

Step 1: Enter the formula shown below in cell B2 and calculate the result. 

=AVERAGEA(A2:A7)

Example-3-Step-1

Step 2: The calculated result value is displayed in cell B2, as shown in the image below.

Example-3-Step-2

While using the AVERAGEA function, text values are treated as 0; for both types of data, the average is calculated using numeric values. 

Hence, the calculation is as follows: (0+1+0+3+0+5)/6 = 1.5.

This can be particularly useful when working with datasets that contain a mix of numerical and textual information. 

Example #4 – Calculate Logical values

In the following illustration, the AVERAGEA function is utilized to calculate the average when logical values are provided.

AVERAGEA-Excel-Example-4

To utilize the AVERAGEA Excel function, please follow these steps:

Step 1: Input the following formula into cell C2 and compute the result.

=AVERAGEA(A2:A5)

Example-4-Step-1

Step 2: The calculated result is displayed in cell C2, as depicted in the image provided. As shown in Column B, the AVERAGEA function interprets the logical values as 1 for TRUE and 0 for FALSE when performing the calculation. Therefore, if any logical values are present in the range being averaged, they will be included in the calculation as either a one or a 0. 

So, the calculation is as follows: (1+0+0+20)/4 = 5.25

Example-4-Step-2

Important Things To Note

  1. The AVERAGEA function treats TRUE as 1 and FALSE as 0 when calculating averages.
  2. Only the values in the array or reference are considered in the argument, while empty cells and text values are disregarded.
  3. To calculate the average of a range of cells without including logical values and text representations of numbers, it is recommended to use the AVERAGE function.

Frequently Asked Questions (FAQs)

1. What types of data can be used with the AVERAGEA function?

The AVERAGEA function in Excel is used with a variety of data types. It can analyze numerical values to calculate their average.

The function can use text and logical values, making it useful for scenarios where non-numeric data is included in the calculation.

The AVERAGEA function is particularly valuable when working with datasets that may contain empty cells or errors, as it will include these values in the calculation without skewing the average result.

2. Are there any limitations or restrictions when using the AVERAGEA function in Excel?

The limitations of using the AVERAGEA Excel function are as follows;

• The AVERAGEA Excel function includes non-numeric values in its calculations, leading to potential inaccuracies if these non-numeric values are unintentionally included.
• The AVERAGEA function does not consider blank cells or errors within a selected range, impacting the overall calculation result.
• The AVERAGEA function considers both numeric and non-numeric values; users must ensure data consistency and accuracy to generate reliable averages.

3. What is the difference between the AVERAGE and AVERAGEA functions in Excel?

AVERAGE and AVERAGEA

Download Template

This article must help us understand the AVERAGEA Excel Function’s formula and examples. You can download the template here to use it instantly.

Guide to AVERAGEA Excel Function. Here we explain how to use AVERAGEA function with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *