Listing of the most useful

SUMIF - Adds all the values in a range that meet specific critera

SUMIFS (2007 and 2010 only) - Adds values in a range based on multiple criteria

SUMPRODUCT - Sum a range of cells that meet multiple criteria

ROUND - Round a number to a specified number of digits

ROUNDUP - Round a number up to a specified number of digits

ROUNDDOWN - Round a number down to a specified number of digits

CEILING - Round a number up to a multiple of significance

FLOOR - Round a number down to a multiple of significance

AVERAGE - Calculates the average number from a range of values

MAX - Finds the maximum value in a range

MIN - Finds the minimum value in a range

COUNTA - Counts all non-empty cells in a range

COUNTBLANK - Counts all blank cells in a range

COUNTIF - Counts all the cells in a range that meet specific critera

COUNTIFS (2007 and 2010 only) - Counts all the cells in a range that meet multiple criteria

AVERAGEIF (2007 and 2010 only) - Calculates the average of a range of values that meet specific criteria

AVERAGEIFS (2007 and 2010 only) - Calculates the average of a range of values that meet multiple criteria

LARGE - Return a value dependent upon its ranking in a range of values in descending order

SMALL - Return a value dependent upon its ranking in a range of values in ascending order

RANK - Returns the rank or position of a number within a range of numbers

REPT - Repeats a character a specified number of times

TRIM - Remove unwanted spaces from cells

LEFT - Extracts a specific number of characters from the start of a cell

RIGHT - Extracts a specific number of characters from the end of a cell

MID - Extracts a specific number of characters from the middle of a cell

UPPER - Converts the contents of a cell to uppercase

LOWER - Converts the contents of a cell to lowercase

PROPER - Converts the contents of a cell to proper case

REPLACE - Replace existing characters in a cell with a different set of characters

SUBSTITUTE - Replace existing characters with a different set of characters

RATE - Returns the interest rate per period of a loan or investment

PV - Returns the present value of an investment based on a constant interest rate and payments

FV - Returns the future value of an investment based on constant payments and a constant interest rate

IPMT - Calculates the interest paid during a period of a loan or investment

PPMT - Calculates the principal payment made in a period of an investment

IRR - Returns the internal rate of return on a series of regular investments

XIRR - Returns the internal rate of return on a series of irregular payments on an investment

NPV - Returns the net present value of an investment based on a series of cash flows and a discount rate

XNPV - Returns the net present value of an investment based on a series of cash flows, the dates of the cash flows and a discount rate

HLOOKUP - Looks horizontally across a list to find a record and returns information related to that record

MATCH - Returns the position of a value in a list

INDEX - Returns an item from a specific position in a list

INDIRECT - Allows you to use a cell reference entered as a text string

OFFSET - Returns a value from a cell, or range of cells that are a specified number of rows and columns from another cell

CHOOSE - Returns a value from a list of values based on a specified position

ADDRESS - Returns a text representation of a cell address from specified row and column numbers

AND - Test up to 30 conditions using logical And

OR - Test up to 30 conditions using logical Or

IFERROR - Performs a specified action if a formula evaluates to an error, and displays the formula result if not

NOW - Returns the current date and time

DATE - Returns the sequential serial number for the specified date and formats the result as a date

DAY - Returns the day corresponding to a date represented by a number between 1 and 31

MONTH - Returns the month corresponding to a date represented by a number between 1 and 12

YEAR - Returns the year corresponding to a date represented by a number in the range 1900 to 9999

WORKDAY - Returns the date a specified number of workings days before or after a date

WEEKDAY - Returns the day of the week corresponding to a specified date

NETWORKDAYS - Returns the number of workdays between two dates

EOMONTH - Calculates the last day of the month a specified number of months before or after a date

**Microsoft Excel functions**. Explanations on how to write each function in Excel and examples of its use are provided.## Mathematical Functions

SUM - Adds up all the values in a rangeSUMIF - Adds all the values in a range that meet specific critera

SUMIFS (2007 and 2010 only) - Adds values in a range based on multiple criteria

SUMPRODUCT - Sum a range of cells that meet multiple criteria

ROUND - Round a number to a specified number of digits

ROUNDUP - Round a number up to a specified number of digits

ROUNDDOWN - Round a number down to a specified number of digits

CEILING - Round a number up to a multiple of significance

FLOOR - Round a number down to a multiple of significance

## Statistical Functions

COUNT - Counts all the values in a rangeAVERAGE - Calculates the average number from a range of values

MAX - Finds the maximum value in a range

MIN - Finds the minimum value in a range

COUNTA - Counts all non-empty cells in a range

COUNTBLANK - Counts all blank cells in a range

COUNTIF - Counts all the cells in a range that meet specific critera

COUNTIFS (2007 and 2010 only) - Counts all the cells in a range that meet multiple criteria

AVERAGEIF (2007 and 2010 only) - Calculates the average of a range of values that meet specific criteria

AVERAGEIFS (2007 and 2010 only) - Calculates the average of a range of values that meet multiple criteria

LARGE - Return a value dependent upon its ranking in a range of values in descending order

SMALL - Return a value dependent upon its ranking in a range of values in ascending order

RANK - Returns the rank or position of a number within a range of numbers

## Text Functions

LEN - Returns the length, in number of characters, of the contents of a cellREPT - Repeats a character a specified number of times

TRIM - Remove unwanted spaces from cells

LEFT - Extracts a specific number of characters from the start of a cell

RIGHT - Extracts a specific number of characters from the end of a cell

MID - Extracts a specific number of characters from the middle of a cell

UPPER - Converts the contents of a cell to uppercase

LOWER - Converts the contents of a cell to lowercase

PROPER - Converts the contents of a cell to proper case

REPLACE - Replace existing characters in a cell with a different set of characters

SUBSTITUTE - Replace existing characters with a different set of characters

## Financial Functions

PMT - Calculates loan repayments based on constant payments and a constant interest rateRATE - Returns the interest rate per period of a loan or investment

PV - Returns the present value of an investment based on a constant interest rate and payments

FV - Returns the future value of an investment based on constant payments and a constant interest rate

IPMT - Calculates the interest paid during a period of a loan or investment

PPMT - Calculates the principal payment made in a period of an investment

IRR - Returns the internal rate of return on a series of regular investments

XIRR - Returns the internal rate of return on a series of irregular payments on an investment

NPV - Returns the net present value of an investment based on a series of cash flows and a discount rate

XNPV - Returns the net present value of an investment based on a series of cash flows, the dates of the cash flows and a discount rate

## Lookup and Reference Functions

VLOOKUP - Looks vertically down a list to find a record and returns information related to that recordHLOOKUP - Looks horizontally across a list to find a record and returns information related to that record

MATCH - Returns the position of a value in a list

INDEX - Returns an item from a specific position in a list

INDIRECT - Allows you to use a cell reference entered as a text string

OFFSET - Returns a value from a cell, or range of cells that are a specified number of rows and columns from another cell

CHOOSE - Returns a value from a list of values based on a specified position

ADDRESS - Returns a text representation of a cell address from specified row and column numbers

## Logical Functions

IF - Tests a condition and takes an alternative action depending on the resultAND - Test up to 30 conditions using logical And

OR - Test up to 30 conditions using logical Or

IFERROR - Performs a specified action if a formula evaluates to an error, and displays the formula result if not

## Date & Time Functions

TODAY - Returns the current dateNOW - Returns the current date and time

DATE - Returns the sequential serial number for the specified date and formats the result as a date

DAY - Returns the day corresponding to a date represented by a number between 1 and 31

MONTH - Returns the month corresponding to a date represented by a number between 1 and 12

YEAR - Returns the year corresponding to a date represented by a number in the range 1900 to 9999

WORKDAY - Returns the date a specified number of workings days before or after a date

WEEKDAY - Returns the day of the week corresponding to a specified date

NETWORKDAYS - Returns the number of workdays between two dates

EOMONTH - Calculates the last day of the month a specified number of months before or after a date

## No comments:

## Post a Comment