Excel Formulas

5.0 (2 reviews)
Term
1 / 58
A1
Click the card to flip 👆
Terms in this set (58)
A1
Both the column and row references are "relative" and will change when the reference is copied and pasted to other cells
$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.
$A$1
Both the column and row references are "absolute" and will remain constant when the reference is copied and pasted to other cells.
COUNT
(value1, [value2], ...) - counts the number of cells in a range of cells that contain numbers
COUNTA
(value1, [value2], ...) - counts the number of cells in a range of cells that are not blank
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
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.
EFFECT(nominal_rate, npery) - calculates the annual percentage rate for an interest rate given the number of times per year that interest is charged.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.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.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.Trace Precedentsallows you to see which cells are referenced in a particular formula or functionTrace Dependentsallows you to see which other cells reference a particular cellShow Formulasdisplays the functions and formulas used in each cell of a worksheet instead of the formula or function resultsAND(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 FALSENOT(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.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.COUNTIF(range,criteria) - allows you to determine the number of cells within a range of cells that contain a specific valueSUMIF(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 calculatedAVERAGEIF(range,criteria,[average_range]) - used to calculate the average for a set of values that match a specific criterionVLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])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.TODAY() - returns the current dateNOW() - returns the current timeDAY(serial_number) - returns the day portion of a date (a number between 1 and 31)MONTH(serial_number) - returns the month portion of a date (a number between 1 and 12)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 dateWEEKNUM(serial_number, [return_type]) - returns the week of the year for a dateWEEKDAY return type1 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 type1 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)HOUR(serial_number) - returns the hour portion of a time as a number from 0 to 23MINUTE(serial_number) - returns the minute portion of a time as a number from 0 to 59SECOND(serial_number) - returns the second portion of a time as a number from 0 to 59LEN(text) - returns the length, in number of characters, of a block of textSEARCH(find_text,within_text, [start_num]) - returns the position of a specific character, word, or phrase within a block of textLEFT(text,[num_chars]) - returns a specified number of characters starting from the beginning of a block of textMID(text,start_num,num_chars) - returns a specified number of character from the middle of a block of textRIGHT(text,[num_chars]) - returns a specified number of characters starting from the end of a block of textUPPER(text) - converts a block of text to all upper-case charactersLOWER(text) - converts a block of text to all lower-case charactersPROPER(text) - converts a block of text to title-case (the first letter of each new word is capitalized)CONCATENATE(text1,[text2],...) - combines blocks of textSUBSTITUITE(text,old_text,new_text,[instance_num]) - replaces specified characters, words, or phrases within a block of text with new characters, words, or phrasesMonthly IncomeThe monthly income is based on the annual income inputted by the user. The monthly income is the annual income divided by 12.Acceptable Income Servicing DebtThe 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 DebtsThis is included from the inputs to be used as part of the maximum payment calculation in the model.Max PaymentThis is the total loan payment the applicant can make and still be approved for the loan. It is calculated as the acceptable income servicing debt minus the other monthly debts the applicant must pay.Monthly PaymentThis is the monthly payment for the proposed loan. It is calculated using the PMT function that accounts for the loan amount, interest rate, and term of the loan as defined in the inputs section of the model.Scenario ManagerThe scenario manager is useful for examining the impact of several inputs to a spreadsheet model changing at the same time. Scenario manager is sophisticated enough to allow you to create a number of different scenarios based on different states of the same set of inputs. In this way, you can compare a number of different hypothetical situations at the same time.Goal Seekis used to work backwards from an outcome calculation to determine an input condition necessary to arrive at the desired outcome. Goal seeks allows the user to specify the desired value for an outcome cell and select an input cell that Excel should modify to achieve the desired outcome.Data Tableswe can specify a number of different possible states for one or two input variables and construct a table in our worksheet that calculates an outcome for our model based on each state of the input variablesPivotTablesPivotTables are used to organize and summarize large amounts of data. PivotTables also allow us to change how we summarize data to look at it in different ways - that's where the name comes from. We can look at the data one way and then easily "Pivot" or change how we examine the data. This allows us to perform some fairly sophisticated analyses quickly and easily