AVERAGEA FUNCTION

The AVERAGEA function returns the average of a set of data. The AVERAGEA function differs from the AVERAGE function in the fact that it uses text and logical results as a part of its calculation of the average. The AVERAGEA function can be broken down into three categories:

Data Set

TRUE

FALSE

TRUE

FALSE

TRUE

·         There can be a maximum of 255 values in a set of data.

·         AVERAGEA with calculate the average the same exact way as AVERAGE if the data set only contains numbers.

·         A value of TRUE in a cell returns a value of one for the calculation purposes of this function.

·         A Value of False or any text-based values will return a value of zero for the calculation purposes of this function.

Sum of Data Set

1+0+1+0+1

=   3

Average

Sum of Data set		3	=    0.6
Number of Data Points		5

So what does this show? Well if you had a test with five true or false questions, the AVERAGEA function will show the percent of true answers on the test. In the example above the average was 0.6, when multiplied by 100 you get 60% of the test was true.

AVERAGE FUNCTION

The AVERAGE function returns the average of a set of data. The AVERAGE function can be broken down into three categories:

Data Set

90

91

84

93

97

·         There can be a maximum of 255 values in a set of data.

Sum of Data Set

90+91+84+93+97

=   455

Average

Sum of Data set		455	=    91
Number of Data Points		5

So what does this show? Well if you had five students with grades of 90, 91, 84, 93 and 97 on a test, you could use the AVERAGE function to determine what the class average is for the test. This data can be used to compare to other classes to see how well multiple classes did verse similar classes. The class average will show which students need more work and which students stand out above the rest of their class.

AVEDEV Function

The AVEDEV function Returns the average of the absolute deviations of data points from their mean. The AVEDEV function can be down into five parts:

Data Set

90

91

84

93

97

Mean (average of data set)

Sum of data set		90+91+84+93+97	=    91
Number of data points		5

 Deviation of Data Points (difference between mean and data point)

Data	90	91	84	93	97
Mean	91	91	91	91	91
Deviation	-1	0	-7	2	6

Absolute Deviation of Data Points (absolute value of the deviation of data point)

Data	90	91	84	93	97
Mean	91	91	91	91	91
Deviation	-1	0	-7	2	6
Absolute Deviation	1	0	7	2	6

*Note the absolute value of a number is just the positive version of that number. In simple terms just remove any negative sign if there is one.

AVEDEV (absolute deviations of data points from mean)

Sum of Absolute Deviations		1+0+7+2+6	~    3.667
Number of Data Points		6

So what does this show? Well if you had five students with grades of 90, 91, 84, 93 and 97 on a test, you could use the AVEDEV function to determine how far each individual student is from the class average. You can also use this to identify if one piece of data is so far off the rest of the data.  Statisticians use this function to determine how accurate a set of data is when testing various data for experimentation's.