These are the principal spreadsheet functions used in this course. You can click on any function name or argument to view an example. This website is not intended as a replacement for your spreadsheet user's manual. Instead, it should simply serve as a supplement to your manual.

ABS( cell )

Absolute value of a single cell or numeric formula

AVEDEV( range )

Mean absolute deviation of a set of data

AVERAGE( range )

Simple arithmetic mean of a set of data

CHOOSE( index , action-1 , action-2 , ... )

Case statement. Index should be a positive integer. Action-X can be any legal Excel statement

CORREL( x-range , y-range )

Correlation coefficient between two variables. X-Y order is not important.

COUNT( range )

Number of numeric values in a set of data

COUNTA( range )

Number of non-blank values in a set of data

COVAR( x-range , y-range )

Covariance between two variables. X-Y order is not important.

DAVERAGE( table , column , criteria )

Database table computation of the conditional arithmetic mean of the values in a specified column

DCOUNT( table , column , criteria )

Database table computation of the conditional number of numeric values in a specified column

DCOUNTA( table , column , criteria )

Database table computation of the conditional number of non-blank values in a specified column

DGET( table , column , criteria )

Database table conditional retrieval of a single value in a specified column

DMAX( table , column , criteria )

Database table computation of the conditional maximum of the values in a specified column

DMIN( table , column , criteria )

Database table computation of the conditional minimum of the values in a specified column

DSTDEV( table , column , criteria )

Database table computation of the conditional sample standard deviation of the values in a specified column

DSTDEVP( table , column , criteria )

Database table computation of the conditional population standard deviation of the values in a specified column

DSUM( table , column , criteria )

Database table computation of the conditional sum of the values in a specified column

DVAR( table , column , criteria )

Database table computation of the conditional sample variance of the values in a specified column

DVARP( table , column , criteria )

Database table computation of the conditional population variance of the values in a specified column

FV( rate , periods , amount , initial , type )

Future value of an annuity

HLOOKUP( key , table , row )

Horizontal table lookup

IF( test , true-action , false-action )

Logical test. True-False actions can be any legal Excel statement.

AND( test-1 , test-2 , ... )

Logical and. Result is true if all tests are true. It is false if any test is false.

OR( test-1 , test-2 , ... )

Logical or. Result is true if any test is true. It is false if all tests are false.

INT( cell )

Integer truncation (i.e., round down) of a single cell or numeric formula

INTERCEPT( y-range , x-range )

Intercept of linear regression equation for predicting Y

LARGE( range , k )

K-th largest value in a set of data

MAX( range )

Largest value in a set of data

MEDIAN( range )

Median of a set of data

MIN( range )

Smallest value in a set of data

MODE( range )

Mode of a set of data

NORMSDIST( z )

Normal distribution probability associated with a given Z score (i.e., Table B)

NOW( )

Current time and date

PV( rate, periods, amount, initial, type )

Present value of an annuity

RAND( )

Randomly generated number between zero and one

RANK( value , range , order )

Rank (i.e., sequence order) of a given value in a set of data

ROUND( cell , digits )

Rounded value of a single cell or numeric formula

RSQ( x-range , y-range )

Coefficient of determination between two variables. X-Y order is not important.

SLOPE( y-range , x-range )

Slope of linear regression equation for predicting Y

SMALL( range , k )

K-th smallest value in a set of data

SQRT( cell )

Square root of a single cell or numeric formula

STANDARDIZE( x , mean , stdev )

Z score of X from a distribution with a given mean and standard deviation

STDEV( range )

Sample standard deviation of a set of data

STDEVP( range )

Population standard deviation of a set of data

SUM( range )

Sum of a set of data

TODAY( )

Current date

VAR( range )

Sample variance of a set of data

VARP( range )

Population variance of a set of data

VLOOKUP( key , table , column )

Vertical table lookup

Additional Information

Getting Started WithExcel

Menu Bar

Button Bars

Cell Ranges

Relative & Absolute Addressing

Drag Copying

Function Wizard

Chart Wizard

Dfunctions

Excel Example

    Home | Lectures | Handouts | Assignments |

This website was originally developed by John Mote for his MIS 311F class. This site is now maintained by the Department of Civil Engineering at the University of Memphis. Your comments and questions are welcomed.