The statistical functions of MS Excel can come useful to a person who is dealing with a lot of data that is numerical in nature. Here, we will look at two functions that are variations of the AVERAGE function in excel.
Statistical Functions – AVERAGEA
This function can be used to calculate the average value of a list of numbers but unlike AVERAGE, this function will consider values like true or false and numbers that are written as text.
Suppose Hari has the following data. He can calculate the average using AVERAGEA function.
Syntax:
AVERAGEA(value1,value2,…)
where ‘value1’ refers to the cell reference where the value is present.
The formula he can use is
=AVERAGEA(A1:A5)
The result will appear as follows.
Alternatively, he can go to the ‘Formulas’ menu and click on the ‘More Functions’ option of the ‘Function Library’. From the dropdown list he can choose ‘Statistical’ and then choose ‘AVERAGEA’. The following dialogue box will appear.
He can enter the suitable cell references or manually select these by clicking on the arrow beside the text boxes. He should click ‘OK’ once done.
Statistical Functions – AVERAGEIF
This function can be used to calculate the average value of a list of numbers that satisfies a certain condition.
Syntax:
AVERAGEIF(Range,criteria,[average_range])
where ‘Range’ refers to the cell references where the values maybe present, ‘criteria’ to the condition specified and ‘average_range’ to the cells which should be averaged.
Suppose he wants to find the average of numbers above 43,
The formula he can use is
=AVERAGEIF(A1:A5,”>43″)
The result will appear as follows.
Please note that ‘TRUE’ and ‘FALSE” functions are informed in this case.
Alternatively, he can go to the ‘Formulas’ menu and click on the ‘More Functions’ option of the ‘Function Library’. From the dropdown list he can choose ‘Statistical’ and then choose ‘AVERAGEIF’. The following dialogue box will appear.
He can enter the suitable cell references or manually select these by clicking on the arrow beside the text boxes. He should click ‘OK’ once done.
AVERAGEIFS can be used instead of AVERAGEIF if he has to compute averages for values that satisfy more than one criteria.