Excel Statistical Functions

1. AVERAGE Function

The Excel AVERAGE Function is listed under Microsoft Excel’s Statistical Functions category. It returns the average of the numeric values you have entered in the function. In simple words, it sums all the values you specify in the function and then divides it by the count and returns the result.

Syntax

=AVERAGE(number1,number2,…)

Arguments

  • number1: The first number that you want to use for the average calculation.
  • [number2]: The second number that you want to use for the average calculation.

Example

To master the AVERAGE function we need to try it out in an example and below is one which you can try out:

excel-average-function-example-1

In the first example, we have entered arguments directly into the function.

In the second example, we have referred to a range that has numbers. You can refer to the unlimited cell by using a continuous range and if you want to refer to a dynamic range you can use a table for that.

You can refer to the unlimited cell by using a continuous range and if you want to refer to a dynamic range you can use a table for that.

In the third example, we have referred to a range in which cells are formatted as a text value. In this case, you can convert those text numbers into the actual numbers to calculate average.

In the fourth example, we have an apostrophe before every value in each cell and hence ignored by the function.

2. AVERAGEA Function

The Excel AVERAGEA Function is listed under Microsoft Excel’s Statistical Functions category. It returns the average of the numbers you specify into the function, but unlike AVERAGE and AVERAGEA, it considers boolean values and numbers which are formatted as text.

Syntax

=AVERAGEA(value1,value2,…)

Arguments

  • value1: A value which is a number, a logical value or a number that is stored as a text.
  • [value2]: A value which is a number, a logical value or a number that is stored as a text.

Example

To master the AVERAGEA function we need to try it out in an example and below is one which you can try out:

excel-averagea-function-example-1

The value returned by the function is 10.17 that is “(0+0+1+10+20+30)/6”.

3. AVERAGEIF Function

The Excel AVERAGEIF Function is listed under Microsoft Excel’s Statistical Functions category. It returns the average of numbers that meet the condition you specify. In simple words, it only considers and calculates the average of numbers that fulfill the condition.

Syntax

= AVERAGEIF(range, criteria, average_range)

Arguments

  • range: A range that contains values from which you want to check the condition.
  • criteria_range: A range of cells from which you want to test the condition.
  • criteria: A condition that you want to mention.

Example

In the below example, we have used AVERAGEIF to calculate the average of product C and we have directly inserted criteria into the function by using double-quotes.

excel-averageif-function-example-1

4. AVERAGEIFS

The Excel AVERAGEIFS Function is listed under Microsoft Excel’s Statistical Functions category. It returns the average of numbers that meet multiple conditions you specify. Unlike AVERAGEIF, you can set multiple conditions and can average only for those numbers which meet all the conditions.

Syntax

= AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

  • criteria_range1: A range of cells from which you want to test the condition.
  • criteria1: A condition that you want to test.
  • [criteria_range2]: The second range of cells from which you want to test the condition.
  • [criteria1]: A second condition that you want to test.

Example

To master the AVERAGEIFS function we need to try it out in an example and below is one which you can try out:

In the below example, we have used the AVERAGEIFS function to calculate the average quantity sold by salesman “Peter” and for the product “B”. We have inserted criteria directly into the function and we have two entries of Peter’s sale of product B.

excel-averageifs-function-example-1

In the below example, we have used AVERAGEIFS with an asterisk to calculate the average price of fruits whose quantity is greater than 20 units and has B in its name.

In the below data, we have two fruits that meet these criteria.

excel-averageifs-function-example-2

5. COUNT Function

The Excel COUNT Function is listed under Microsoft Excel’s Statistical Functions category. It returns a count of the numbers from the values you specify. In simple words, it only considers values which are numbers and return their count in the result.

Syntax

= COUNT(value1, [value2], …)

Arguments

  • value1: A cell reference, an array or a number that is directly entered into the function.
  • [value2]: A cell reference, an array or a number that is directly entered into the function.

Example

To master the COUNT function we need to try it out in an example and below is one which you can try out:

In the below example, we have used this function to count cells from the range A1:A10 and it has return 8 in the result.

excel-count-function-example-1

In cell A3, we have a logical value and in cell A7 I have a text. COUNT has ignored both of the cells. But if you enter a logical value directly into the function it will count it. In the below example, we have inserted a logical value and a number using double quotation marks.

excel-count-function-example-2

6. COUNTA Function

The Excel COUNTA Function is listed under Microsoft Excel’s Statistical Functions category. It returns a count of the values you specify. Unlike, COUNT, it considers all types of values but ignores (Cells) which are blank. In simple words, all the cells are not blank.

Syntax

= COUNTA(value1, [value2], …)

Arguments

  • value1: A value, a cell reference, a range of cells, or an array.
  • [value2]: A value, a cell reference, a range of cells, or an array

Example

To master the COUNTA function we need to try it out in an example and below is one which you can try out:

In the below example, we have used COUNTA function to count cells from range A1:A11.

excel-counta-function-example-1

There is a total of 11 cells in the range and function has return 10. I have one blank cell in the range which is ignored by the function. In the rest of the cells, we have numbers, text, logical value and a symbol.

7. COUNTBLANK

The Excel COUNTBLANK Function is listed under Microsoft Excel’s Statistical Functions category. It returns the count of cells that are blank or has no value. In simple words, it won’t count cells that contain text, numbers, errors, but count formulas that return a blank value.

Syntax

= COUNTBLANK(range)

Arguments

  • range: A range of cells from which you want to count blank cells.

Example

To master the COUNTBLANK function we need to try it out in an example and below is one which you can try out:

In the below example, we have used COUNTBLANK function to count blank cells from the range A2:A8.

excel-countblank-function-example-1

In this range, we have a total of 3 cells that are blank but cell A7 has a formula in it which results in a blank cell.

The function has returned 2 as cell A4 and A5 are the only cells that are blank and have no values.

8. COUNTIF

The Excel COUNTIF Function is listed under Microsoft Excel’s Statistical Functions category. It returns a count of the numbers which meet the condition you specify. In simple words, it only considers and calculates the count of values that fulfill the condition.

Syntax

= COUNTIF(range, criteria)

Arguments

  • range: A range of cells from which you want to count the cells which meet criteria.
  • criteria: A criteria (Case Sensitive) to check to count cells.

Example

To master the COUNTIF function we need to try it out in an example and below is one which you can try out:

1. Using Logical Operators as Criteria

In the below example, we have used “>75” (a logical operator) to count the number of students who scored more than 75.

excel-countif-function-example-1

If you want to use a logical operator you have to put it inside the double quotation marks.

2. Using Dates as a Criteria

In the below example, we have used a date in criteria to find out how many employees have a week off on 4th July.

excel-countif-function-example-2

When you insert a date directly into the function, COUNTIF automatically converts a text into a date.

In the below example, we have inserted the same date as a number and as you know Excel stores a date as a number.

excel-countif-function-example-3

So you can also insert a number which represents a date as per Excel’s date system.

9. COUNTIFS

The Excel COUNTIFS Function is listed under Microsoft Excel’s Statistical Functions category. It returns the count the numbers which meet multiple conditions you specify. Unlike COUNTIF, you can set multiple conditions and can count only those numbers which meet all those conditions.

Syntax

= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Arguments

  • criteria_range1: The range of cells that you want to evaluate using criteria1.
  • criteria1: The criteria which you want to evaluate for criteria_range1.
  • [criteria_range2]: The range of cells that you want to evaluate using criteria1.
  • [criteria2]: The criteria which you want to evaluate for criteria_range1.

Example

To master the COUNTIFS function we need to try it out in an example and below is one which you can try out:

In the below example, we have used COUNTIFS to count females who are above 25 years of age.

And I have specified two criteria for evaluation, one is “Female” and the other is a greater than operator to count cells with a number which is greater than “>25”.

excel-countifs-function-example-1

In the below example, we have used an asterisk in one criterion and > operator in another one to count the number of the person whose name is starting with alphabet A and whose age is more than 25.

excel-countifs-function-example-2

10. MAX

The Excel MAX Function is listed under Microsoft Excel’s Statistical Functions category. It returns the largest value from a list of values. MAX stands for maximum and when you specify a list of value it searches for the value which is the highest in it and returns that value in the result.

Syntax

= MAX(number1, [number2], …)

Arguments

  • number1: A number, a cell that has a number or a range of cells having numbers from which you want to get the largest number.
  • [number2]: A number, a cell that has a number or a range of cells having numbers from which you want to get the largest number.

Example

To master the MAX function we need to try it out in an example and below is one which you can try out:

In the below example, I have entered numbers directly into the function by separating them using a comma.

excel-max-function-example-1

Note: You can also enter a number by using double quotation marks.

In the below example, we have referred to a range and it has returned 1861 in the result as the largest value. You can also refer to an array as well.

excel-max-function-example-2

In the below example, we have encountered an error value and function has returned an error value in the result.

excel-max-function-example-3

11. MIN

MIN function returns the lowest value from a list of values. MIN stands for minimum and when you specify a list of values it searches for the value which is the lowest in it and returns that value in the result.

Syntax

= MIN(number1, [number2], …)

Arguments

  • number1: A number, a cell that has a number, or a range of cells having numbers from which you want to get the smallest number.
  • [number2]: A number, a cell that has a number, or a range of cells having numbers from which you want to get the smallest number.

Example

In the below example, we have entered numbers directly into the function by separating them using a comma.

excel-min-function-exmple-1

You can also enter a number by using double quotation marks. Now in the below example, we have referred to a range and it has returned 1861 in the result.

excel-min-function-exmple-2

In the below example, we have encountered an error value and function has returned an error value in the result.

excel-min-function-exmple-3