In Excel, a function can be described as a built in tool for performing mathematical or logical tests. Quite often, you may need to perform operations in your worksheets that involve many cells, like totalling a lengthy column of numbers or averaging a large group of data. When dealing with financial data, you might have to evaluate loan amortizations or future values. In statistical applications, you might have to determine the standard deviation or variance for a group of numbers. Excel functions can help you with all of these tasks.

 

Functions can also be used to perform searches for specific values, or to perform operations based on conditions that you set.

 

Operations that are complex and involve many cell references can be difficult or even impossible to implement with basic arithmetic formulas. Thankfully, Excel provides a wide range of built in functions that help make even very complex or repetitive calculations easy.

 

Excel’s functions are broken down into the following categories. Under each heading is a list of some of the more common functions that belong to that category.

 

Financial Functions

 

These functions perform common financial tasks, like finding future values, calculating loan payments, calculating depreciation, and finding interest paid over a time period.

 

·              DB   finds the depreciation of an asset based on the fixed declining balance method

·              DDB finds the depreciation of an asset using the double declining balance method

·              FV   calculates a future value based on constant payments and constant interest rate

·              IPMT finds the interest payment based on constant payments and constant interest rate

·              ISPMT finds the interest paid on an investment over a specific period

·              NPER finds the number of periods for an investment based on constant interest and payments

·              NPV  calculates the net present value of an investment

·              PMT  calculates loan payments based on a constant interest rate

·              PPMT calculates the payment on the principle for an investment based on constant payments and interest rate

·              PV  gives the present value of an investment

·              RATE  finds the interest rate per period on a loan or investment

 

Date and Time Functions

 

These functions provide representations and conversion options for dates and times.

 

·              DAY returns the number of the day from 1 to 31

·              DAYS360 calculates the number of days between two dates based on 360 day years

·              HOUR gives the hour as a number from 0 to 23

·              MINUTE gives the minute as a number from 0 to 59

·              MONTH gives the month as a number from 1 to 12

·              NOW gives the current date and time

·              SECOND gives the second as a number from 0 to 59

·              TIME  converts hours minutes and seconds to an Excel serial number time

·              TODAY  provides the current date

·              WEEKDAY gives the day as a number from 1 to seven

·              YEAR  gives the year of a serial number date, from 1900 to 9999

 

 

Mathematical and trigonometric functions

 

These functions are intended for mathematical calculations that can involve logarithms, trigonometry, rounding, or matrices.

 

·              ABS   Gives the absolute value of a number

·              ACOS  calculates the arc cosine of a number in radians

·              ASIN  calculates the arc sin

·              ATAN  calculates the arc tangent of a number in radians

·              CEILING rounds a number up to the nearest integer

·              COMBIN  calculates the number of possible combinations

·              COS  calculates the cosine of a given angle

·              EVEN rounds a number up to the nearest even integer

·              EXP raises the mathematical constant e to a given power

·              FACT calculates the factorial of a number

·              FLOOR rounds a number down to the nearest significant number

·              INT rounds a number down to the nearest integer

·              LN  finds the log to the base e of a given number

·              LOG  finds the log to any given base for a given number

·              LOG10 finds the log to the base 10 for a given number

·              MINVERSE finds the inverse of a matrix

·              MMULT returns the product of two matrices

·              MOD returns the remainder when a given number is divided by a another number

·              ODD rounds a number up to the nearest odd integer

·              PI  gives the value of the mathematical constant pie to 14 decimal places

·              POWER  raises a given number to a given power

·              PRODUCT  multiplies a series of numbers

·              RAND generates a random number between 1 and 0

·              ROMAN  converts to roman numerals

·              ROUND  will round a number to a specified number of digits

·              ROUNDDOWN  rounds a number down

·              ROUNDUP rounds a number up

·              SIGN will indicate if the number is negative positive or zero

·              SIN  will calculate the sine of an angle

·              SQRT will calculate the square root of a number

·              SUBTOTAL  gives the subtotal for a list of data

·              SUM calculates the sum for a range of cells

·              SUMIF calculates the sum of cells that satisfy a given condition

·              SUMPRODUCT calculates the sum of the products of corresponding cell ranges

·              TAN  gives the tangent of an angle

·              TRUNC  truncates a floating point number to an integer

 

 

 

Statistical Functions

 

Excel 2003 has a wide range of statistical functions including many that have been improved from previous versions. You can use these functions to find averages, counts, medians, probabilities, standard deviations, and much more. The following is a sample of some of the more common statistical functions in Excel.

 

·              AVERAGE - returns the mean for a range of numbers

·              AVERAGEA - returns the mean for a range of values that might include text or logical values

·              BINOMDIST - gives a probability based on the binomial distribution

·              CONFIDENCE - calculates the confidence interval for a population mean

·              CORREL - calculates the correlation between two ranges of data

·              COUNT - will count the number of cells in a list that contain numbers

·              COUNTIF - will count the number of cells in a list that satisfy a specified condition

·              LARGE - will find the nth largest number in a set of numbers, where n can be any given number

·              MAX - returns the largest number from a range of numbers

·              MAXA - finds the largest value in a set of values that may include text or logical values

·              MEDIAN - finds the median of a range of numbers

·              MIN - finds the minimum number in a range of numbers

·              MINA - finds the minimum number in a range of numerical , text, or logical values

·              MODE - gives the most frequently occurring number in a set of numbers

·              PERCENTRANK - gives the rank of a number as a percentage of the numbers in the range of data

·              PERMUT - calculates the number of permutations for given numbers

·              RANK - gives the rank of a number relative to the other numbers in the dataset based on size

·              SMALL - returns the nth smallest number from a range of numbers for a given n

·              STDEV - gives an estimate of the standard deviation based on a sample

·              STDEVP - calculates the standard deviation for an entire population

·              VAR - will estimate the variance based on a sample

·              VARP - will calculate the variance of an entire population

 

Lookup and Reference Functions

 

The lookup and reference functions can help you gather information about cell ranges and references and determine the location of specific data elements in a range. Some of the more important lookup and reference functions are

 

·              COLUMN - finds the column number for a reference

·              COLUMNS - tells you the number of columns in a given range

·              HLOOKUP - finds a specified value in the top row of a range, and from the same column, returns a value from a specified row

·              HYPERLINK - creates a hyperlink to a document stored locally, on your network, or the internet

·              INDIRECT - returns the value associated with a given text reference.

·              LOOKUP - looks up a specified value in a one row or one column range of data

·              ROW - finds the row number for a given reference

·              ROWS - tells you the number of rows in a given range

·              VLOOKUP - finds a specified value in the far left column of a table and returns from the same row, a value from a column you specify

 

 

Database Functions

 

Database functions allow you to search for and perform operations on data in a table or list, according to conditions that you specify. Some useful database functions are:

 

·              DAVERAGE - averages values in a column according to conditions you specify

·              DCOUNT - count cells that contain numbers matching conditions you specify

·              DGET - gets a record from an Excel database matching conditions that you specify

·              DMAX - gets the largest number from a column in your Excel database where the number satisfies conditions you specify

·              DMIN - retrieves the smallest number that meets your conditions from a column in the database

·              DSUM - sums numbers in a database that satisfy conditions you specify

 

Text Functions

 

Text functions help you manipulate individual characters and strings of characters that are entered in a worksheet as text. Some useful text functions are

 

·              CLEAN - removes all characters that cannot be printed from the text

·              CONCATINATE - joins together strings of text into one larger string

·              DOLLAR - converts a number to currency formatted text

·              EXACT - will test two text strings to see if they are exactly the same

·              FIND - will find the starting location of a string of characters within a larger string

·              LEFT - returns a specified number of characters from the start (left ) of a string

·              LEN - gives the number of characters in a text string

·              LOWER - converts any uppercase letters in a string to lowercase

·              REPLACE - will replace a part of a string with another string

·              RIGHT - will give you the specified number of characters from the end or right of a string

·              T - tests if a cell value is text or not

·              TEXT - converts a value to number formatted text

·              TRIM - removes all extra spaces from a text string ( spaces between words will stay)

·              UPPER - converts a text string to uppercase

 

Logical Functions

 

The logical functions allow you to perform logical tests and build logical expressions based on the arguments you provide. You can test conditions, and proceed according to the result.

 

·              AND - will return the logical value true if all of the arguments you specify are true, and will return a logical value of false otherwise

·              FALSE - will return the logical value false

·              IF - will test if a condition that you set is true, and return a specified value if it is, and another specified value if it isn’t

·              NOT - will change logical values from true to false or false to true (not true is false, and not false is true)

·              OR - will return a logical value of true if any of the arguments are true and a value of false if both all arguments are false

·              TRUE - returns the logical value of true

 

 

Information Functions

 

Information functions can help you gather information about cell values, formatting, errors, and even your current operating environment. Some of Excel’s information functions are:

 

·              CELL - provides information on the formatting, location, or contents of the upper left cell in a specified range

·              INFO - provides information about your operating environment

·              ISBLANK - tests if a reference refers to an empty cell

·              ISERROR - will test if a cell value is one of Excel’s error messages (you can specify which one)

·              ISNUMBER - tests if a value in a cell is a number

·              ISREF - tests if a value in a cell is a reference

·              ISTEXT - tests if a value in a cell is text

·              TYPE - gives a number representing the type of value in a cell ( 1=number, 2=text, 4=logical value, 16=error, 64= array