Formulas Overview
"Formulas perform calculations and return the results that are displayed in the cell.”
Elements of a Formula
Cell References: Relative, Absolute, Mixed, External/3D
The default reference used by Excel.
Ex. C2, D2, BZ45, AAB531
Special kind of reference that doesn’t change when evoked from different areas.
Ex. $C$2, $D$2, $BZ$45, $AAB$531
Either row or column is relative, and the other is absolute.
Ex. $C2, D$2, $BZ45, AAB$531
Can be from different workbooks in the same or different worksheets.
Ex. =SheetName!CellAdress, =[WorkbookName]SheetName!CellAddress
Operators: Reference, Arithmetic, Text Concatenation, Comparison/Logical
Range Operator (:), Union Operator (,), Intersection Operator (Space), Negation (-).
Addition (+), Subtraction/Negation (-), Multiplication (*), Division (/), Percent (%), Exponentiation (^).
Connect or Concatenate Two Values to Produce One Continuous Text Value (&).
Equal to (=), Greater than (>), Less than (<), Greater than or Equal to (>=), Less than or Equal to (<=), Not equal to (<>).
Values: Text, Dates, Numbers, Boolean Data…
Ex. 12341235, lsdkjfnlsakjfdnflaksjdnf, TRUE, FALSE, 11/30/2004, etc.
Functions: Lookup, Math, Financial, Conditional…
“--” turns boolean values TRUE and FALSE to numeric values 1 and 0.
Can be thought of as predefined formulas.
=VLOOKUP (value, table, col_index, [range_lookup])
Lookup a value in a table by matching on the first column.
value: the value to look for in the first column of a table.
table: the table from which to retrieve a value.
col_index: the column you are trying to get the return value from.
[range_lookup]: TRUE for approximate match (default). FALSE for an exact match.
=HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]):
The same as VLOOKUP except it looks up the value horizontally and matches the returning value vertically instead of vertically and horizontally like in VLOOKUP.
=INDEX(array, row_num, [col_num], [area_num]):
Returns the value or values at given positions in a range or array. Can return an entire column, row, or value in a specified cell.
array: a range of cells, or an array constant.
row_num: the row position in that reference or array.
[col_num]: the column position in the reference or array.
[area_num]: the range in reference that should be used.
=MATCH(lookup_value, lookup_array, [match_type]):
Locates the position of a lookup value in a row, column, or table. Supports approximate and exact matching. Often used with the INDEX function to retrieve a value at a matched position.
lookup_value: the value to match in a lookup_array.
lookup_array: a range of cells or an array reference.
[match_type]: 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.
=OFFSET(reference, rows, cols, [height], [width]):
Returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, (5) a width in columns. Creates a reference offset from the given starting point.
reference: the starting point, supplied as a cell reference or range.
rows: the number of rows to offset below the starting reference.
cols: the number of columns to offset to the right of the starting reference.
[height]: the height in rows of the returned reference.
[width]: the width in columns of the returned reference.
Ex. To reference the value in C5 starting at A1, you would use =OFFSET(A1, 4, 2).
=SUM(number1, [number2], [number3], …):
Returns the sum of the supplied values.
number1: the first value to sum.
[number2]: the second value to sum.
[number3]: the third value to sum.
=AVERAGE(number1, [number2], [number3], …):
Calculates the arithmetic mean of the arguments. Can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.
number1: a number or cell reference that refers to numeric values.
[number2]: a number or cell reference that refers to numeric values.
=MEDIAN(number1, [number2], [number3], …):
Returns the median in a group of supplied numbers.
number1: a number or cell reference that refers to numeric values.
[number2]: a number or cell reference that refers to numeric values.
=SUMPRODUCT(array1, [array2], [array3], …):
Multiplies ranges or arrays together and returns the sum of products. You can also use this for scenarios where you have to sum up values with two or more conditions.
array1: the first array or range to multiply, then add.
Ex. =SUMPRODUCT(--(B2:B5="Luke"), --(C2:C5="West"),D2:D5) finds how many units “Luke” sold in the region “West”.
=PRODUCT(number1, [number2], …):
Returns the product of the arguments/values passed to it.
number1: the first number or range to multiply.
=ROUNDDOWN(number, num_digits):
Returns number rounded down to a given number of places.
number: the number to round down.
num_digits: the number of digits to which number should be rounded down.
Ex. =ROUNDDOWN(3.7, 0) rounds down 3.7 to 0.
=ROUNDUP(number, num_digits):
Same thing as ROUNDDOWN except, you guessed it, it rounds up.
=ROUND(number, num_digits):
Same thing except it rounds normally.
=ABS(number):
Returns the absolute value of number.
=SUMIF(range, criteria, [sum_range]):
Returns the sum of the cells in range as long as it complies with the given criteria.
range: the range of cells you want to apply the criteria against.
criteria: the criteria used to determine which cells to add.
[sum_range]: the cells to add together. If sum_range is omitted, the cells in range are added together instead.
Ex. =SUMIF(D6:D10, “>100”) adds all the values in D6:D10 that are >100. Condition has to be included within quotation, “”, marks.
=SUMSQ(number1, [number2], …):
Returns the sum of the squares of the values provided.
number1: the first argument containing numeric values.
[number2]: another argument containing numeric values.
=PI():
Returns Pi.
=NPV(rate, value1, [value2], … ):
Calculates the net present value of cash flows based on a discount rate. Used to determine the profitability of a venture. If the sum of all cash flows, after having been issued the discount factor, is positive, it means that it’s made up the initial investment.
rate: discount rate over one period.
value1: first value(s) representing cash flows.
[value2]: second value(s) representing cash flows.
=XNPV(rate, values, dates):
Calculates the NPV of an investment using a discount rate and a series of cash flows that occur at irregular intervals.
rate: discount rate to apply to the cash flows.
values: values representing cash flows.
dates: dates that correspond to cash flows.
=IRR(values, [guess]):
Returns the Internal Rate of Return (IRR) for a series of cash flows that occur in regular intervals. The IRR is the discount rate that makes the NPV 0.
values: array of reference to cells that contains values.
[guess]: an estimate for expected IRR. Default is .1 (10%).
=XIRR(values, dates, [guess]):
Returns the IRR for a series of cash flows that occur at irregular intervals.
values: array or reference to cells that contain cash flows.
[guess]: an estimate for expected IRR. Default is .1 (10%).
=YIELD(sd, md, rate, pr, redemption, frequency, [basis]):
Returns the yield on a security that pays periodic interest. No clue what that means.
sd: settlement date of the security.
md: maturity date of the security.
rate: annual coupon rate.
pr: security’s price per $100 face value.
frequency: coupon payments per year (annual = 1, semiannual = 2; quarterly = 4).
[basis]: day count basis (default = 0).
=FV(rate, nper, pmt, [pv], [type]):
You can use the FV function to get the future value of an investment assuming periodic, constant payments with a constant interest rate.
rate: the interest rate per period.
nper: the total number of payment periods.
pmt: the payment made each period. Must be entered as a negative number.
[pv]: the present value of future payments. If omitted, assumed to be zero. Must be entered as a negative number.
[type]: when payments are due. 0 = end of period, 1 = beginning of period, default = 0.
=PV(rate, nper, pmt, [fv], [type]):
You can use the PV function to get the value in today’s dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate.
rate: the interest rate per period.
nper: the total number of payment periods.
pmt: the payment made each period.
[fv]: a cash balance you want to attain after the last payment is made, default = 0.
[type]: when payments are due. 0 = end of period, 1= beginning of period, default = 0.
=INTRATE(settlement, maturity, investment, redemption, [basis]):
settlement: settlement date of the security.
maturity: maturity date of the security.
investment: the amount originally invested.
redemption: the amount received at maturity.
[basis]: day count basis, default = 0.
=IPMT(rate, per, nper, pv, [fv], [type]):
Calculates the interest portion of a given loan payment in a given payment period. You can use IPMT to get the interest amount of a payment on the first period, the last period, or any period in between.
rate: the interest rate per period.
per: the payment period of interest.
nper: the total number of payment periods.
pv: the present value, or total value of all payments now.
[fv]: the cash balance desired after last payment is made, default = 0.
[type]: when payments are due, 0 = end of period, 1 = beginning of period, default = 0.
=PMT(rate, nper, pv, [fv], [type]):
Returns the periodic payment for a loan. You can use the PMT function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.
rate: the interest rate for the loan.
nper: the total number of payments for the loan.
pv: the present value, or total value of all loan payments now.
[fv]: the cash balance desired after last payment is made, default = 0.
[type]: when payments are due, 0 = end of period, 1 = beginning of period, default = 0.
=PRICE(sd, md, rate, yld, redemption, frequency, [basis]):
Returns the price per $100 face value of a security that pays periodic interest.
sd: settlement date of the security.
md: maturity date of the security.
rate: annual coupon rate.
yld: annual required rate of return.
redemption: redemption value per $100 face value.
frequency: coupon payments per year (annual = 1, semiannual = 2; quarterly = 4).
[basis]: day count basis, default = 0.
=DB(cost, salvage, life, period, [month]):
Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
cost: initial cost of asset.
salvage: asset value at the end of depreciation.
life: periods over which asset is depreciated.
period: period to calculate depreciation for.
[month]: number of months in the first year, default = 12.
=DDB(cost, salvage, life, period, [factor]):
Calculates depreciation based on the double-declining balance method.
=SLN(cost, salvage, life):
Calculates depreciation based on the straight-line method.
=IF(logical_test, [value_if_true], [value_if_false]):
Runs a logical test and returns one value for a TRUE result, another for a FALSE result.
logical_test: a value of logical expression that can be evaluated as TRUE or FALSE.
[value_if_true]: the value to return when the logical test evaluates to TRUE.
[value_if_false]: the value to return when the logical test evaluates to FALSE.
=OR(logical1, [logical2], …):
Checks if any conditions, logical1, logical2, …, are met and returns TRUE or FALSE.
=XOR(logical1, [logical2], …):
“Exclusive OR”, returns TRUE if either statement is TRUE, but returns FALSE if both statements are TRUE.
=AND(logical1, [logical2], …):
Returns TRUE if all conditions are TRUE, but FALSE otherwise.
=NOT(logical):
Changes “TRUE” to “FALSE”, and “FALSE” to “TRUE”.
=IFERROR(value, value_if_error):
Returns a custom result, value_if_error, if value results in an error.
Excel Error Values: #DIV/0!, #NAME?, #N/A, #NULL!, #NUM!, #REF!, #VALUE
There are eight different error values that Excel will return when there is something wrong with a formula or with the referenced cells. Will not be discussing ######## because it doesn't require any further information besides what's been provided by the chart.
#DIV/0!: Tries to divide by zero. Because Excel applies a value to 0 to empty cells, this error also occurs when the formula attempts to divide by an empty cell.
#NAME?: Uses a name that Excel doesn’t recognize. Can happen if you delete a name that’s used in the formula. If you misspell a name and then hit Enter, or if you have unmatched quotes when using text.
#N/A: Refers to a cell that uses the NA function to signal that data is not available. For instance, =IF(A1 = “ “, NA(), A1) returns a #N/A error if A1 is empty.
#NULL!: Uses an intersection of two ranges that don’t intersect.
#NUM!: Problem with a value exists. For example, you specified a negative number as the argument where a positive number is required.
#REF: Refers to a cell that isn’t valid. Can happen if the cell has been deleted from the worksheet.
#VALUE: Includes an argument or operand of the wrong type.
Comments
Post a Comment