Examples with functions AVERAGE and AVERAGEA for Excel

AVERAGE and AVERAGEA functions are used to calculate the arithmetic average of the arguments of interest in Excel. The average is calculated in the classical way - summing all the numbers and dividing the sum by the number of these numbers. The main difference between these functions is that they process differently non-numeric types of values that are in Excel cells. More about everything in more detail.



Examples of using the AVERAGE function in Excel

AVERAGE function refers to the Statistical group. Therefore, to call this function in Excel, you must select the tool: “FORMULAS” - “Functions Library” - “More functions” - “Statistical” - “AVERAGE”. Or call the "Insert Function" dialog box (SHIFT + F3) and select the "Statistical" option from the "Category:" drop-down list. After that, in the “Select function:” field there will be a list of categories with statistical functions where the AVERAGE is located, as well as AVERAGEA.

AVERAGEA.

If there is a range of B2:B8 cells with numbers, then the formula =AVERAGE(B2:B8) will return the average value of the given numbers in this range:

FORMULA.

The syntax is the following: =AVERAGE(number1,[number2],...), where the first number is a required argument, and all subsequent arguments (up to the number 255) are optional for filled. That is, the number of selected source ranges cannot exceed more than 255:

syntax.

The argument can have a numeric value, be a reference to a range or array. Text and logical values in the range are completely ignored.

The arguments of the AVERAGE function can be represented not only by numbers, but also by names or references to a specific range (cell) containing a number. The logical value and textual representation of the number, which is directly entered into the argument list, is taken into account.

If the argument is represented by a reference to a range (cell), then its text or logical value (reference to an empty cell) is ignored. In this case, cells that contain zero are counted. If the argument contains errors or text that cannot be converted to a number, then this results in a common error. To take into account logical values and the textual representation of numbers, it is necessary to use in the calculations the AVERAGEA function, which will be discussed further.

test.

The result of the function in the example in the picture below is the number 4, since logical and text objects are ignored. Therefore:

(5 + 7 + 0 + 4) / 4 = 4

When calculating averages, you need to take into account the difference between a blank cell and a cell containing a zero value, especially if the "Show a zero in cells that have zero values" option is cleared in the Excel dialog box. When checked, empty cells are ignored, but zero values are not. To remove or set this flag, open the “FILE” tab, then click on “Options” and select in the category “Advanced” group “Display options for this worksheet:”, where it is possible to check the box:

Options.

The results of another 4 tasks are summarized in the table below:

results.

As you can see in the example, in cell A9, the AVERAGE function has 2 arguments: 1 - a range of cells, 2 - an additional number 5. The same can be specified in the arguments and additional ranges of cells with numbers. For example, as in cell A11.



Formulas with examples of using the AVERAGEA function

AVERAGEA function differs from AVERAGE in that the true logical value of “TRUE” in the range is equal to 1, and the false logical value of “FALSE” or the text value in the cells is equal to zero. Therefore, the result of calculating the AVERAGEA function is different:

AVERAGEA.

The result of the function returns the number in the example 2,833333, since the text and logical values are taken as zero, and the logical TRUE is equal to one. Consequently:

(5 + 7 + 0 + 0 + 4 + 1) / 6 = 2.83

Syntax:

=AVERAGEA(value1,[value2],...)

The arguments of the AVERAGEA function are subject to the following properties:

  1. "Value1" is mandatory, and "value2" and all values that follow it are optional. The total number of cell ranges or their values can be from 1 to 255 cells.
  2. The argument can be a number, a name, an array, or a reference containing a number, as well as a textual representation of the number or a logical value, for example, “true” or “false”.
  3. The logical value and textual representation of the number entered in the argument list is taken into account.
  4. An argument containing the value “true” is interpreted as 1. An argument containing the value “false” is interpreted as 0 (zero).
  5. Text contained in arrays and references is interpreted as 0 (zero). An empty text ("") is also interpreted as 0 (zero).
  6. If the argument is an array or reference, then only the values included in this array or reference are used. Empty cells and text in the array and reference are ignored.
  7. Arguments that are error values or text that cannot be converted to numbers cause errors.

The results of the feature of AVERAGEA are summarized in the table below:

Formulas with examples.

Download examples with functions AVERAGE for Excel

Attention! When calculating the mean values in Excel, you need to take into account the differences between a blank cell and a cell containing a zero value (especially if the “Show a zero in cells that have zero values” in “Option” is unchecked in the options dialog box). When checked, empty cells are not counted, and zero values are counted. To check the box on the “FILE” tab, select the “Option” command, go to the “Advanced” category, where find the section “Show options for the next sheet” and set the flag there.


en ru