Statistical Function in Excel

Statistical Function in Excel

In this article you will learn about Statistical Function in Excel that includes AVERAGE, AVERAGEIF, MODE, MEDIAN, Standard Deviation, MIN, MAX, Large and Small Function in Excel.

Statistical analysis and functions is another attractive feature of MS Excel. These function sets are very much useful for statistical analysis and operations. MS Excel has a variety of functions available, however, this tutorial will focus on the most frequently used functions.

AVERAGE

AVERAGE function is used to determine the average of the given range. In the example below, you will observe that the below formula is calculating average for range A1:O1 and the answer is produced in cell A4 (i.e. 5).

average

AVERAGEIF

AVERAGEIF function is a conditional function used to determine the average of the given range based on the given criteria. In the example below, you will observe that the below formula is calculating average for range A1:O1 on the basis of criteria that function will be applied to only Non-Zero cells. Therefore, cell A1 and cell N1 have been excluded from this function and hence the answer is produced in cell C4 (i.e. 5.77).

averageif

MEDIAN

The median function is used to determine the middle value from the given range. To find the median (or middle number), use the MEDIAN function. In the example below, you will observe that the media has been determined for range A1:O1 and 6 is the median figure.

median

Verification:

To verify this, all the numbers of the range arrange are placed in order and cell H1 falls at the middle which has the value 5.

the value 5

MODE

The mode function is used to determine the most frequently occurring number in a given range. In the example below, you will observe that number 4 has the most occurrence within this range.

mode

Standard Deviation

STEDV function is used to calculate the standard deviation in MS Excel. In the example below, you will observe that the STDEV function is applied on range A1:O1 and the result is produced on cell J4.

stdev

MIN

MIN function is used to determine the minimum value in a given range. In the example below, you will observe that the MIN function is applied on range A1:O1 and the resultant value 0 is shown in cell A4.

min

MAX

MAX function is used to determine the maximum value in a given range. In the example below, you will observe that the MAX function is applied on range A1:O1 and the resultant value 9 is shown in cell C4.

max

LARGE

LARGE function is used to determine the maximum value in a given range. However, the level of max value can be set in LARGE function. In the example below, you will observe that the LARGE function is applied on range A1:O1 to determine the 3rd large value in the given range and the resultant value 8 is shown in cell E4.

large

Verification:

To verify this, all the numbers of the range arrange are placed in order and cell M1 has the 3rd largest value of the range which is 8.

range which is 8

SMALL

SMALL function is used to determine the minimum value in a given range. However, the level of min value can be set in SMALL function. In the example below, you will observe that the SMALL function is applied on range A1:O1 to determine the 3rd smallest value in the given range and the resultant value 3 is shown in cell G4.

small

Verification:

To verify this, all the numbers of the range arrange are placed in order and cell B1 has the 3rd smallest value of the range which is 3.

range which is 3