Excel Functions

Objectives:

• Use functions to calculate min, max, sum, count, mean, median, mode

Using Excel help (F1):

In What would you like to do? type the name of any of the above functions

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

Simple Statistical Functions

Using simple statistical functions

Excel has some built-in functions that can be used to provide statistical information regarding that data in a list.

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.

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

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

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

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

Mode(<values>). Finds the mode of the cells in the list.

Example: Calculate statistics for the advising list

The following examples assume that the column with the number of credits for each student has been named using Excel's "Insert" | "Name" | "Define" command.

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

=Sum(credits)

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

=Min(credits)

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

=Max(credits)

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

=Average(credits)

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(credits)

Try this and see what happens:

=Count(FirstName)

To find out which number of credits occurs the most frequently:

=Mode(credits)

Updated 2004.08.25