Statistical functions: avedev, average, averagea, countblank, countif

Excel Function AVEDEV

The AVEDEV() function calculates the average deviation of a given set of values.

The average deviation is the deviation of set of values from their average value.

The formula for calculating the AVEDEV for n number of values represented by x and average value of x

excel statistical functions avedev average averagea countblank countif

A maximum of 255 arguments can be supplied in this function.

Values AVEDEV() Formula
1,2,3,4 1 =AVEDEV(1,2,3,4)
5,56,6,90 33.75 =AVEDEV(5,56,6,90)
34 0 =AVEDEV(L7)
21 6.5 =AVEDEV(L7:L8)
45 14.16 =AVEDEV(L7:L9,4,5)

 

 

Excel Function AVERAGE

The AVERAGE() function is used to calculate the average value of a set of numbers.

This function can take a maximum of 255 values.

The syntax is :

AVERAGE(num1,num2,�num255)

Values AVERAGE() Formula
1,2,3,4 2.5 =AVERAGE(1,2,3,4)
5,56,6,90 39.25 =AVERAGE(5,56,6,90)
34 34 =AVERAGE(L22)
21 27.5 =AVERAGE(L22:L23)
45 30 =AVERAGE(L22:L24,20)

 

 

Excel Function AVERAGEA

The AVERAGEA() function is also used to calculate the average of a set of values.

The difference from AVERAGE() function is that, AVERAGEA() can also include Boolean and number values

represented as text.

Values AVERAGEA() Formula
True 0.5 =AVERAGEA(L33:L34)
False 0 =AVERAGEA(L34)
34 17 =AVERAGEA(L34:L35)
21 33.3333333333333 =AVERAGEA(L35:L36,"45")
45 33.3333333333333 =AVERAGEA(L35:L37)

 

 

Excel Function COUNTBLANK

The COUNTBLANK() function is used to calculate the number of blank cells within a range.

The cells containing text,numbers or any other symbols are not counted.

Even the white space is also not considered as blanks.

Values COUNTBLANK() Formula Remarks
Eating 0 =COUNTBLANK(L45)
Study 0 =COUNTBLANK(L45:L47)
1 =COUNTBLANK(L47:L48) White space
1 =COUNTBLANK(L47:L49) Blank cell
45 1 =COUNTBLANK(L45:L49)

 

 

Excel Function COUNTIF

The COUNTIF() function is used to count the number of cells within a range that satisfies a certain criteria.

This function returns a number that represents cell count matching the supplied criteria.

The syntax is as follows:

COUNTIF(range,criteria)

Values COUNTIF() Formula Remarks
Eating 0 =COUNTIF(L57:L58,"Vaishu")
Study 0 =COUNTIF(L57:L58,"nag") Not case sensitive
0 =COUNTIF(L57:L59,"") White space
0 =COUNTIF(L57:L61,1) Blank cell
45 1 =COUNTIF(L57:L61," ")

 

You can find similar Excel Questions and Answer hereunder

1) how do you enter multiple line in a cell in Excel

2) statistical functions gammainv gammaln geomean growth harmean intercept

3) Concatenate number with text but keeping in number format in Excel

4) Formula does not calculate in Excel

5) Calling a macro from another workbook in Excel

6) Is there a way I can average a range of numbers even if there is an error value in range?

7) Remove the apostrophe cell text values in Excel

8) Vba to return week numbers in Excel

9) statistical functions maxa median mina mode normdist norminv

10) how to combine the content of two or more cells in Excel

 

Here the previous and next chapter