michigan informatics

Selected Functions

AVERAGE  COUNTA  COUNTIF  LEFT (or RIGHT)  STDEV  SUMIFM

Most examples provided below are based on the range C3:C7 from the worksheet sample given below.

AVERAGE

Calculates the mean of a group of arguments (individual cells or a range of cells)

Format:  =AVERAGE(argument1,argument2,…)

Example:  =AVERAGE(C3:C7)                 
Answer: 160.6

COUNTA

Counts the number of cells with content, i.e. not blank, within a group of arguments (individual cells or a range of cells)

Format:  =COUNTA(argument1,argument2,…)

Example:  =COUNTA(C3:C7)                   
Answer:  5

COUNTIF

Counts the number of cells that meet specific criteria within a group of arguments (individual cells or a range of cells).

Format:  =COUNTIF(argument_range, “criteria”)

Example:  =COUNTIF(C3:C7, “>150”)    
Answer:  2

LEFT (or RIGHT)

Returns the left-most (or right-most) characters in a text string.

Format:  =LEFT(text, number_of_characters)

Example =LEFT(123456789,4)               
Answer: 1234

Example:  =RIGHT(123456789,5)                      
Answer: 56789

STDEV

Standard Deviation estimates how widely the values deviate from the average of the arguments (individual cells or a range of cells)

Format:  =STDEV(argument1,argument2,argument3,…)

Example:  =STDEV(C3:C7)                      
Answer: 70.4

SUMIF

Adds a range of arguments (range of cells) that meet specific criteria.

Format:  =SUMIF(argument_range, “criteria”)

Example:  =SUMIF(C3:C7, “<150”)         
Answer: 331