Statistical Functions in Excel 2010 

Excel provides a large selection of Statistical functions. This will help to perform common calculations to complex statistical distribution and probability tests.

Finding the right statistical function in Excel 2010

Follow the below steps to identify the Statistical function in excel.

Step 1:

Click the Formulas tab in excel and then click the insert function.

2

Step 2:

Select the category as Statistical and you can see all the statistical functions.

3

As a result, Excel’s statistical function appears in alphabetic order in select a function box. By this we can find the right statistical function in Excel 2010.

Statistical function examples:

  • MAX
  • MIN
  • LARGE
  • SMALL
  • COUNT
  • COUNTA
  • COUNTBLANK
  • COUNTIF
  • AVERAGE
  • MEDIAN
  • MODE

MAX:

MAX function is used to find the largest value in a set of values. This function ignores logical values and text.

Formula for this function is

=Max(number1, (number2),….)

Number implies set of numeric values or arrays of numeric values that returns the largest value.

You can input the values directly or as cells containing the numeric values or as values returned from other excel formulas.

Statistical functions in excel 2010

Example:

Below screen shot shows the MAX function used to retrieve the largest value in cells A2 to A6.

4

Note:  This function error is likely to be #Value! . This happens when arguments cannot be recognized as numeric values.

MIN:

MIN function is used to find the smallest value in a set of values. This function ignores logical values and text.

Formula for this function is

=Min(number1, (number2),….)

Number implies set of numeric values or arrays of numeric values that returns the smallest value.

You can input the values directly or as cells containing the numeric values or as values returned from other excel formulas.

Example:

Below screen shot shows the MIN function used to retrieve the smallest value in cells A2 to A6.

5

Note: This function is likely to be #Value!. This happens when arguments cannot be recognized as numeric values.

LARGE:

LARGE function is used to find the K-th largest value in a data set.

Formula for this function is

=Large(array,k)

Array implies the set of numerical value you want to return k th largest number.

K implies the index.

You can input the values directly, or as cells containing the numeric values.

Example:

Below screen shot shows the LARGE function used to retrieve the 3rd largest value from the set of values in A2 to A6.

6

Note:This function error is likely to be #Value!.

SMALL:

SMALL function is used to find the K-th smallest value in a data set.

Formula for this function is

=Small(array,k)

Array implies the set of numerical value you want to return k th smallest number.

K implies the index.

You can input the values directly or as cells containing the numeric values.

Example:

Below screen shot shows the SMALL function used to retrieve the 2nd smallest value from the set of values in A2 to A6.

7

Note: This function error is likely to be #Value! .

COUNT:

This function counts the number of cells in a range that contain numbers.

Formula for this function is

=count(value1,(value2),…)

Value implies any value, or references to cell ranges.

Example:

Below screen shot shows the count function used to count the number of values in A2 to A6.

8

Note: Numbers and dates are counted as numerical values. Text and logical values are counted depending whether they are value in range of cells or if they are supplied directly to the function.

COUNTA:

This function counts the number of cells in a range that are not empty.

Formula for this function is

=counta(value1,(value2)….)

Value implies any value or references to cell ranges.

Example:

Below screen shot shows the Counta function used to count  non-blanks in cells A2 to A6

9

In other words it ignores empty cells and counts the data such as numbers, dates, formulas or text tables.

COUNTBLANK:

This function counts the number of empty cells in a specified range of cells.

Formula for this function is

=countblank(range)

Range implies the range of cells in which you want to count blank cells.

Example:

Below screen shot shows the Countblank function used to count blanks in cells A2 to A6.

10

This other words this function considers the empty cells and ignores data such as number, dates, formulas or text tables.

COUNTIF:

This function counts the number of cells within a range that meet the given condition.

Formula for this function is

=countif(range,criteria)

Range implies the range of cells that should be tested against the criteria and counted if the criteria is satisfied.

Criteria implies condition that is tested against each cells in range.

Example:

Below screen shot shows the countif function example

11

Note: if the criteria is text or expression, this must be supplied to the function in quotes.

AVERAGE:

This function returns the average of its arguments, which can be numbers or names, arrays, or references that contains numbers.

Formula for this function is

=Average(number1, (number2)….)

Number implies one or more numeric values for which you want to calculate the average. Numeric values can be supplied directly to the function or as one or more cells or ranges of cells containing numeric values

Example:

Below screen shot shows the Average function calculated for set of cells in A2 to A6.

12

Note: If the values to be averaged are non-numeric then the common error is #DIV/0!

MEDIAN:

This function returns the median or the number in the middle of the set of given numbers.

Formula for this function is

=Median(number1, (number2)…)

Number implies one or more numeric values for which you want to calculate the median. Numeric values can be supplied directly to the function or as one or more cells or ranges of cells containing numeric values

Example:

Below screen shot shows the Median function calculated for set of cells in A2 to A6.

13

Note: Median function ignores text and logical values. If there is even number of values in data set, the average of the middle values is returned.

MODE:

This function returns the most frequently occurring or repetitive value in an array or range of data

Formula for this function is

=mode(number1,(number2)….)

Number implies one or more numeric values for which you want to calculate the mode.

Example:

Below screen shot shows the Mode function calculated for set of values in A2 to A22

14

Note: If there are no duplicates the common error is #NUM! and if the supplied value is non-numeric then the common error is #VALUE!.

Below shows some of the statistical functions with descriptions.

FUNCTION

DESCRITION

MAXA This fuction returns the largest value in a set of values. Only difference between MAX and MAXA function is, the MAX  functions cannot include logical values or text as their arguments whereas, the MAXA functions can include both logical values and Text as their arguments.
MINA This fuction returns the smallest value in a set of values. Only difference between MIN and MINA function is, the MIN  functions cannot include logical values or text as their arguments whereas, the MINA functions can include both logical values and Text as their arguments.
AVERAGE This function returns the average of its arguments, which can be numbers or names, arrays, or references that contain numbers
AVERAGEA This function returns the average of its arguments, evaluating text and FALSE in arguments as 0; True evaluates as 1. Arguments can be numbers, names, arrays, or references
AVERAGEIF This function finds the average supplied array, that satisfy a given condition or criteria
AVERAGEIFS This function finds entries in one or more arrays, that satisfy supplied criteria, and returns the average of the corresponding values in a further supplied array.
MODE.SNGL This function returns the most frequently occuring or repetitive value in an array or range of data
MODE.MULT This function returns a vertical array of the most frequently occuring or repetitive values in an array or range of data.
GEOMEAN This function returns the geometric mean of an array or range of positive numeric data
HARMEAN This function returns the harmonic mean of a data set of positive numbers.
TRIMMEAN This function returns the mean of the interior portion of a set of data values
FREQUENCY This function calculates how often values occur within a range of values and then returns a vertical array of numbers having one or more element than Bins_array.
RANK This function returns the rank of a number in a list of numbers.
RANK.EQ This function returns the rank of a number in a list of numbers. Its size relative to other values in the list. If more then one value has the same rank, the top rank of that set of values is returned.
RANK.AVG This function returns the rank of a number in a list of numbers. Its size relative to other values in the list. If more then one value has the same rank, the average rank is returnes
KURT This function returns the kurtosis of a data set

 

 

 

 

Leave a Reply