The AVERAGEA function

The AVERAGEA function is a statistical function which works very similarly to the AVERAGE function, with the difference being it also calculates text values and TRUE/FALSE values as arguments. Text arguments as well as FALSE evaluate as 0, where TRUE evaluates to 1.

This is the format of the AVERAGEA function:

= AVERAGEA( Range 1, Range 2, Range 3, etc…)

Here is an example of the AVERAGEA function:

= AVERAGEA( “Apple”, TRUE, 2, 7, 5) 

= AVERAGEA( 0, 1, 2, 7, 5) = 3

AVERAGEA function resolving with TRUE(1) and FALSE(0) variables

This function is useful when you have a mix of data which users have entered on a form, where non-numerical results needs to be counted but resolve to lowering the average results of the question.

An example of a functional implementation of this would be having a customer satisfaction form where on a response of 0 (the lowest possible rating), customers are asked to fill out a text field explaining their answers. In your export you only see what was entered, but to calculate the average per question you will still need to evaluate the text fields, something the original average function cannot handle.

= AVERAGEA({ 3, 9, 6, 0, 3, 6, 4, 7, 6, “Returned - was defective”, 9, 10, 3, “Broken.”})

= AVERAGEA(3, 9, 6, 0, 3, 6, 4, 7, 6, 0, 9, 10, 3, 0) = 4