Excel Formulas

VLOOKUP

(lookup_value,table_array,col_index_num,[range_lookup])

PIVOT TABLE

Before to set a pivot table: 1)Tabular format: column hedings with data in each row. 2)No Gaps in the Data ( we have to clear the data) 3) Always use excel tables ( Ctr+T)

A1

Both the column and row references are "relative" and will change when the reference is copied and pasted to other cells

PMT

Calculates the payment for a loan based on constant payments and a constant interest rate

IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE The syntax for the IF function in Microsoft Excel is: IF( condition, [value_if_true], [value_if_false] ) Parameters or Arguments condition The value that you want to test. value_if_true Optional. It is the value that is returned if condition evaluates to TRUE. value_if_false Optional. It is the value that is return if condition evaluates to FALSE.

ISEVEN

Returns TRUE if the number is even

Trace Precedents

allows you to see which cells are referenced in a particular formula or function

Trace Dependents

allows you to see which other cells reference a particular cell

TODAY

() - returns the current date

NOW

() - returns the current time

EFFECT

(nominal_rate, npery) - calculates the annual percentage rate for an interest rate given the number of times per year that interest is charged.

RATE

(nper, pmt, pv, [fv], [type], [guess]) - calculates the interest rate earned for an investment given the number of payments made as part of the investment, the payment amount, and the current value of the investment.

ISBLANK

Checks whether a reference is to an empty cell, and returns TRUE or FALSE

TODAY

Today's date

MEDIAN

returns the median, or the number in the middle of the set of given numbers

SEARCH

(find_text,within_text, [start_num]) - returns the position of a specific character, word, or phrase within a block of text

NOT

(logical) - accepts a single Boolean expression as an argument and returns the opposite of that expression. If the Boolean expression evaluates as TRUE then the result of the NOT function is FALSE. Likewise, if the expression evaluates as FALSE then the result of the NOT function is TRUE.

AND

(logical1,[logical2],...) - will result in TRUE if all of the combined expressions are TRUE. The AND function returns FALSE if any of the combined expressions are FALSE.

OR

(logical1,[logical2],...) - will result in TRUE if any of the combined expressions are TRUE. The OR function returns FALSE if all of the combined expressions are FALSE

IF

(logical_test,[value_if_true],[value_if_false]) - first is a Boolean expression (logical_test) comparing two values. The other two arguments are used to specify the results of the function depending on the results of the Boolean expression. The second argument (value_if_true) is used to specify the result of the IF function if the Boolean expression result is TRUE. The third argument (value_if_false) is used to specify the result of the IF function is the Boolean expression is FALSE.

HLOOKUP

(lookup_value,table_array,row_index_number,[range_lookup]) - The HLOOKUP function assumes that the reference table has been rotated 90 degrees to the right. HLOOKUP will look in the first row of the reference table for a match to the "lookup_value." Excel will then return the corresponding value from the row specified by the "row_index_num" argument as the result of the HLOOKUP function.

AVERAGE

(number1, [number2], ...) - calculates the simple average of a set of numbers

MAX

(number1, [number2], ...) - returns the largest value in a set of numbers

MIN

(number1, [number2], ...) - returns the smallest value in a set of numbers

COUNTIF

(range,criteria) - allows you to determine the number of cells within a range of cells that contain a specific value

AVERAGEIF

(range,criteria,[average_range]) - used to calculate the average for a set of values that match a specific criterion

SUMIF

(range,criteria,[sum_range]) - used to calculate the total for a set of values that match a specific criterion. The first argument is the range of cells within which you want Excel to search for a match to the criterion you set. The second argument defines the criterion used to find matches. The last argument defines the range of cells to be included when the total is calculated

PV

(rate, nper, pmt, [fv], [type]) - calculates the current value (accounting for compounding interest) of an investment given the interest rate, number of payments to be made, and the amount of the payment.

FV

(rate, nper, pmt, [pv], [type]) - calculates the future value of an investment given the interest rate, number of payments to be made, and the amount of the payment.

PMT

(rate, nper, pv, [fv], [type]) - calculates the payment amount for a loan given the interest rate, number of payments to be made to pay off the loan, and the original loan amount.

NPER

(rate, pmt, pv, [fv], [type]) - calculates the number of payments that will be made to pay off a loan given the interest rate, payment amount, and original loan amount.

DAY

(serial_number) - returns the day portion of a date (a number between 1 and 31)

HOUR

(serial_number) - returns the hour portion of a time as a number from 0 to 23

MINUTE

(serial_number) - returns the minute portion of a time as a number from 0 to 59

MONTH

(serial_number) - returns the month portion of a date (a number between 1 and 12)

SECOND

(serial_number) - returns the second portion of a time as a number from 0 to 59

YEAR

(serial_number) - returns the year portion of a date (a number between 1900 and 9999)

WEEKDAY

(serial_number, [return_type]) - returns the day of the week for a date

WEEKNUM

(serial_number, [return_type]) - returns the week of the year for a date

LOWER

(text) - converts a block of text to all lower-case characters

UPPER

(text) - converts a block of text to all upper-case characters

PROPER

(text) - converts a block of text to title-case (the first letter of each new word is capitalized)

LEN

(text) - returns the length, in number of characters, of a block of text

LEFT

(text,[num_chars]) - returns a specified number of characters starting from the beginning of a block of text

RIGHT

(text,[num_chars]) - returns a specified number of characters starting from the end of a block of text

SUBSTITUITE

(text,old_text,new_text,[instance_num]) - replaces specified characters, words, or phrases within a block of text with new characters, words, or phrases

MID

(text,start_num,num_chars) - returns a specified number of character from the middle of a block of text

CONCATENATE

(text1,[text2],...) - combines blocks of text

COUNTA

(value1, [value2], ...) - counts the number of cells in a range of cells that are not blank

COUNT

(value1, [value2], ...) - counts the number of cells in a range of cells that contain numbers

WEEKDAY return type

1 Returns 1 (Sunday) through 7 (Saturday) 2 Returns 1 (Monday) through 7 (Sunday) 3 Returns 0 (Monday) through 6 (Sunday) 11 Returns 1 (Monday) through 7 (Sunday) 12 Returns 1 (Tuesday) through 7 (Monday) 13 Returns 1 (Wednesday) through 7 (Tuesday) 14 Returns 1 (Thursday) through 7 (Wednesday) 15 Returns 1 (Friday) through 7 (Thursday) 16 Returns 1 (Saturday) through 7 (Friday) 17 Returns 1 (Sunday) through 7 (Saturday)

WEEKNUM return type

1 Week begins on Sunday (System 1) 2 Week begins on Monday (System 1) 11 Week begins on Monday (System 1) 12 Week begins on Tuesday (System 1) 13 Week begins on Wednesday (System 1) 14 Week begins on Thursday (System 1) 15 Week begins on Friday (System 1) 16 Week begins on Saturday (System 1) 17 Week begins on Sunday (System 1) 21 Week begins on Monday (System 2)

SUM

Adds all the numbers in a range of cells (We use this a LOT to roll up trial balances and sub ledgers

SUMIF

Adds the cells specified by a given criteria (We use this a LOT to roll up trial balances and sub ledgers) SYNTAX The syntax for the SUMIF function in Microsoft Excel is: SUMIF( range, criteria, [sum_range] ) Parameters or Arguments range The range of cells that you want to apply the criteria against. criteria The criteria used to determine which cells to add. sum_range Optional. It is the cells to sum. If this parameter is omitted, it uses range as the sum_range.

SUMIFS

Adds the cells specified by a given set of criteria or conditions (We use this a LOT to roll up trial balances and sub ledgers)

$A$1

Both the column and row references are "absolute" and will remain constant when the reference is copied and pasted to other cells.

ISERROR

Checks whether a value is an error, and returns TRUE or FALSE

AND

Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE

OR

Checks whether any of the arguments are TRUE, and then returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE

TRANSPOSE

Converts a vertical range of cells to a horizontal range, or vise versa

COUNT

Counts the number of cells in a range that contain numbers

COUNTIFS

Counts the number of cells specified by a given set of conditions or criteria

COUNTIF

Counts the number of cells within a range that meet the given condition

AVERAGEIF

Finds average for the cells specified by a given condition or criteria

AVERAGEIFS

Finds average for the cells specified by a given set of conditions or criteria

CONCATENATE

Joins several text strings into one text string

HLOOKUP

Looks for a value in the top row of a table or array of values and returns the value in the same column for a row you specify Parameters or Arguments value The value to search for in the first row of the table. table Two or more rows of data that is sorted in ascending order. index_number The row number in table from which the matching value must be returned. The first row is 1. not_exact_match Optional. It determines if you are looking for an exact match based on value. Enter FALSE to find an exact match. Enter TRUE to find an approximate match, which means that if an exact match if not found, then it will look for the next largest value that is less than value. If this parameter is omitted, it will return an approximate match. Note: If index_number is less than 1, the HLOOKUP function will return #VALUE!. If index_number is greater than the number of columns in table, the HLOOKUP function will return #REF!. If you enter FALSE for the not_exact_match parameter and no exact match is found, then the HLOOKUP function will return #N/A.

VLOOKUP

Looks for the value in the leftmost column of a table, and then returns a value in the same row from a column you specify In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position. The syntax for the LOOKUP function in Microsoft Excel is: LOOKUP( value, lookup_range, [result_range] ) Parameters or Arguments value The value to search for in the lookup_range. lookup_range A single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range. result_range Optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data. Note: If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A. If the values in the LOOKUP_range are not sorted in ascending order, the LOOKUP function will return the incorrect value.

RANK

Ranks the rank of a value within a list of values

TRIM

Removes all spaces from text string, except for single spaces between words

ISODD

Returns TRUE if the number is odd

RANDBETWEEN

Returns a random number between the numbers you specify

RAND

Returns a random number greater than or equal to 0 and less than 1, evenly distributed

MID

Returns a specific number of characters from a text string starting at the position you specify

GETPIVOTDATA

Returns data stored within a PivotTable

AVERAGE

Returns the average of its arguments, which can be numbers or names, arrays, or references that contain numbers

COLUMN

Returns the column number of a reference

CORREL

Returns the correlation coefficient between two data sets

LARGE

Returns the k-th largest value in a data set

SMALL

Returns the k-th smallest value in a data set

MAX

Returns the maximum value in a list of arguments

LEN

Returns the number of characters in a text string

TIME

Returns the number that represents a particular time

INDIRECT

Returns the reference specified by a text value

ROW

Returns the row number of a reference

MIN

Returns the smallest value in a list of arguments

LEFT

Returns the specified number of charters from the start of a text string

DB

Returns the straight line depreciation amount of an asset for a given period

IFNA

Returns the value you specify if the formula returns the #N/A error value; otherwise returns the result of the formula.

IFERROR

Returns value_if_error if expression is an error and the value of the expression itself otherwise

ROUNDDOWN

Rounds a number down, toward zero

ROUND

Rounds a number to a specified number of digits (Not only how to do it, but what is does. This impacts when you have to foot and cross foot something in excel. Easy to be off a dollar.)

ROUNDUP

Rounds a number up, away from zero

$A1

The column reference is "absolute" and will remain constant when copied and pasted to other cells. The row reference is "relative" and will change when copied and pasted to cells in other rows of the worksheet.

A$1

The column reference is "relative" and will change when copied and pasted to cells in other columns in the worksheet. The row reference is "absolute" and will remain constant when copied and pasted to other cells.

Acceptable Income Servicing Debt

The maximum dollar amount that the applicant can spend each month on debt to be approved for the loan. It is calculated by multiplying the monthly income by the acceptable percentage of monthly income allowed to go towards debt defined in the model inputs.

Monthly Income

The monthly income is based on the annual income inputted by the user. The monthly income is the annual income divided by 12.

FV

This formula calculates the final value of several future payments =FV(rate, nper, pmt)

PV

This formula indicates whether or not an investment will generate sufficient returns in the future =PV(rate, nper, pmt)

Show Formulas

displays the functions and formulas used in each cell of a worksheet instead of the formula or function results