Excel 2016: Basic Statistical Functions

Updated 2017.07.30

Objectives:

·        Learn how to use functions to calculate the Sum, Average, Count, Max, and Min of a range of cells.

 

Sample data file: Advising.txt. Note that this is a text file and must be "imported" using the Text Import Wizard.

Simple Statistical Functions

Excel has some built-in functions that can be used to provide statistical information regarding that data in a list. Five of the most often-used functions are available on the ribbon. To access these functions from the ribbon:

·        Click on the Home tab.

·        In the Editing group, click on the down-arrow on the Autosum button.

·        Select the desired function.

a

 

 

Sum(<values>). Sums all of the values that are listed. Note that the values can be individual cells (e.g. B10), or ranges (e.g. b10:D30), or named ranges (e.g. "credits"), or any combination with commas separating adjacent cells/ranges.

 

Average(<values>). Finds the average of the values in the list.

 

Count(<values>). Counts the number of cells in the list.

 

Max(<values>). Finds the maximum of the values in the list.

 

Min(<values>). Finds the minimum of the values in the list.

Example: Calculate statistics for the advising list

Sum

To find out the total number of credits for all students:

=Sum(E2:E44)

Average

To find out the average number of credits for any student:

=Average(E2:E44)

Count Numbers

To find out the number of students in the list (note that while it appears that we could count any column here, these functions only work on cells with numbers in them):

=Count(E2:E44)

Max

To find out the largest number of credits for any student:

=Max(E2:E44)

Min

To find out the smallest number of credits for any student:

=Min(E2:E44)