Excel Formulas Cheat Sheet

choose

=CHOOSE(index_num, value1, [value2], ...) Choose takes in multiple cells and an index num. The index num represents the corresponding item in the value list. index_num = 1 will display value 1 etc... The value can be an array or a single cell =CHOOSE(2,A1:A10,B1:B10,C1:C10)

column

=COLUMN([reference]) returns the column numbre. For example column(D12) returns 4 because D is the 4th column. column() returns the column number of the current cell

Countif

=COUNTIF(range, criteria) Counts number of occurrences of criteria =COUNTIF(A2:A5,"London")

effect

=EFFECT(nominal_rate, npery) Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year. Npery is the number of compounding periods per year

fv

=FV(rate,nper,pmt,[pv],[type]) calculates the future value of an investment based on a constant interest rate.

Index Match

=INDEX(C3:E11,MATCH(H2,B3:B11,0),MATCH(H3,C2:E2,0)) Can use index and match together to dynamically select values in row/col/both. Useful for handling different cases in a financial model.

Index

=INDEX(array, row_num, [column_num]) Takes in an array (can be 1d or 2d) and returns the value at the index (row_num, col_num). Indexing starts at cell (1, 1) in the top left. =INDEX(A2:B3,2,2)

let

=LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...]) the let function takes in a variable, the variables initial value, and then some calculation and applies that calculation to the variables initial value (can also be done on multiple variables at once) =LET(x, 5, SUM(x, 1)) When this formula is input into a cell, it will return the value 6.

Match

=MATCH(lookup_value, lookup_array, [match_type]) Takes in a lookup value (i.e. "base case"), an array (must be 1d), and a match type (see below), and returns the index in the array that value appears (index starts at 1). =MATCH(39,B2:B5,1) _______match_type_____ 1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. 0 MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order. -1 MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

nper

=NPER(rate,pmt,pv,[fv],[type]) Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Offset

=OFFSET(reference, rows, cols, [height], [width]) This function takes in a reference cell (a single cell that you use as a starting point) and takes in the number of rows and columns you offset from that starting cell from. There are optional height and width options (default is 1 for both) that selects the number of cells in that area it will return. For example, if height = 2 and width = 3, then it will return all the cell values (and fill surrounding cells from where the formula is being filled) to the excel sheet =OFFSET(D3,3,-2,1,1)term-13

pmt and ipmt and ppmt

=PMT(rate, nper, pv, [fv], [type]) calculates the payment for a loan for a period based on constant payments and a constant interest rate. IPMT(rate, per, nper, pv, [fv], [type]) calculates just the interest expense for that payment PPMT(rate, per, nper, pv, [fv], [type]) calculates just the principle payment for that period

rate

=RATE(nper, pmt, pv, [fv], [type], [guess]) Returns the interest rate per period of an annuity. Nper Required. The total number of payment periods in an annuity. Pmt Required. The payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.

row

=ROW([reference]) returns the row number. For example row(D4) returns 4 because 4 is the 4th row. row() returns the row number of the current cell

sumif

=SUMIF(range, criteria, [sum_range]) sums values if value in range meets criteria. If you add a sum_range, it will check if criteria is in range, then add the value in the corresponding sum range instead of just the range =SUMIF(A2:A5,">160000",B2:B5) Sum of the commissions for property values over $160,000. $63,000 =SUMIF(A2:A5,">160000")

sumproduct

=SUMPRODUCT(array1,array2,array3, ...) multiplies corresponding components in two or more arrays, and returns the sum of those products. arrays must be the same length

text

=TEXT(Value you want to format, "Format code you want to apply") The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It's useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. =TEXT(1234.567,"$#,##0.00")

If

IF(logical_test, value_if_true, [value_if_false]) Checks logical comparison, returns first value if true and second value if false Examples: =IF(A2>B2,"Over Budget","OK") =IF(A2=B2,B4-A4,"")

hlookup

Placeholder

lookup

Placeholder

vlookup

Placeholder