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