Excel Formulas

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

LEN

Returns the number of characters in a text string

TIME

Returns the number that represents a particular time

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)

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)

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.

ISBLANK

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

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

ISEVEN

Returns TRUE if the number is even

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

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

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

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)

TODAY

Today's date

MEDIAN

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