AVERAGEA

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

Returns the arithmetic mean of the values in a list, range or array (including logical values and text).

value1The first value.
value2(Optional) The second value.

REMARKS
* This function is identical to the AVERAGE function except that cell references to logical values and text are included.
* This function includes hidden cells.
* This function excludes empty cells. See Example 8.
* Arguments that are just numbers are included.
* Arguments that are just numbers passed as text ("3") are included. See Example 15.
* Arguments that are just logical values are included. True evaluates to 1. False evaluates to 0. See Example 16.
* Arguments that are just text will generate a #VALUE! error. See Example 18.
* Arguments that are references to cells containing zeros are included. See Example 9.
* Arguments that are references to cells containing dates are included and the date serial number is used. See Example 12.
* Arguments that are references to cells containing logical values are included. See Example 11.
* Arguments that are references to cells containing text are included and 0 is used. See Example 10.
* Arguments that are references to cells containing error values will be returned. See Example 14.
* Arguments that are arrays containing logical values are excluded. See Example 20.
* Arguments that are arrays containing text are included and 0 is used. See Example 19.
* You can have a maximum of 255 arguments.
* You can use the AVERAGEAVISIBLE - User Defined Function to exclude hidden cells.
* This is similar to the COUNTA, MAXA and MINA functions.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 AB
1=AVERAGEA(1,2,3) = 25
2=AVERAGEA(1,2,3,0) = 1.510
3=AVERAGEA(-2,-3,4,5,6) = 215
4=AVERAGEA({-2,-3,4,5,6}) = 2 
5=AVERAGEA(B1:B3) = 100
6=AVERAGEA(B1,B2,B3) = 10text
7=AVERAGEA(B1,B2,B3, B4) = 10True
8=AVERAGEA(B1,B2,B3, B5) = 7.5False
9=AVERAGEA(B1,B2,B3, B6) = 7.5=DATE(2024,1,1) = 01 Jan 2024
10=AVERAGEA(B1,B2,B3,B5) = 7.75=10/0 = #DIV/0!
11=AVERAGEA(B1,B2,B3, B8) = 7.5 
12=AVERAGEA(B1,B2,B3, B9) = 11330.5 
13=AVERAGEA(named_range) = 10 
14=AVERAGEA(B1,B2,B3, B10) = #DIV/0! 
15=AVERAGEA(2,"3") = 2.5 
16=AVERAGEA(2,TRUE) = 1.5 
17=AVERAGEA(2,FALSE) = 1 
18=AVERAGEA(2,"some text") = #VALUE! 
19=AVERAGEA({2,"some text"}) = 1 
20=AVERAGEA({2,TRUE}) = 2 

1 - What is the average (or arithmetic mean) of these values (1,2,3). 6 divided by 2.
2 - What is the average of these values (1,2,3,0). 6 divided by 3.
3 - What is the average of these values (-2,-3,4,5,6). 10 divided by 5.
4 - What is the average of the values in the array {-2,-3,4,5}. 10 divided by 5.
5 - What is the average of the values in the range "B1:B3". 30 divided by 3.
6 - What is the average of the values in the range "B1", "B2" and "B3". 30 divided by 3.
7 - What is the average of the values in the range "B1", "B2", "B3" and "B4". B4 is empty. Empty cells are always excluded. 30 divided by 3.
8 - What is the average of the values in the range "B1", "B2", "B3" and "B5". B5 is zero so this gets included. 30 divided by 4.
9 - What is the average of the values in the range "B1", "B2", "B3" and "B6". B6 is text so this gets included. Text is replaced with 0. 30 divided by 4.
10 - What is the average of the values in the range "B1", "B2", "B3" and "B7". B7 is a logical value so this gets included. TRUE is replaced with 1. 31 divided by 4.
11 - What is the average of the values in the range "B1", "B2", "B3" and "B8". B8 is a logical value so this gets included. FALSE is replaced with 0. 30 divided by 4.
12 - What is the average of the values in the range "B1", "B2", "B3" and "B9". B9 is a date so this gets included as a date serial number.
13 - What is the average of the values in the named range that refers to the range "B1:B3". 30 divided by 3.
14 - What is the average of the values in the range "B1", "B2", "B3" and "B10". B10 contains an error so that gets returned.
15 - What is the average of these values (2, "3"). Text values that are actually numbers are included. 5 divided by 2.
16 - What is the average of these values (2, TRUE). Logical values passed are arguments are included. TRUE is replaced with 1. 3 divided by 2.
17 - What is the average of these values (2, FALSE). Logical values passed as arguments are included. FALSE is replaced with 0. 2 divided by 2.
18 - What is the average of these values (2, "some text"). Text values passed as arguments generate an error.
19 - What is the average of the values in the array {2, "some text"}. Text values inside arrays are included. Text is replaced by 0. 2 divided by 2.
20 - What is the average of the values in the array {2, TRUE}. Logical values inside arrays are excluded. 2 divided by 1.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top