Chapter 2 - Formulas and Functions in Excel Flashcards
Chapter Summary objective
Use semi-selection to create a formula
Semiselection is a pointing process where you click or drag to select cells to add cell references to a formula.
Use relative, absolute, and mixed cell references in formulas.
Cell references within formulas are relative, absolute, or mixed. A relative reference indicates a cell’s location relative to the formula cell. When you copy the formula, the relative cell reference changes. An absolute reference is a permanent pointer to a particular cell, indicated with dollar signs before the column letter and row number, such as $B$5. When you copy the formula, the absolute cell reference does not change. A mixed reference contains part absolute and part relative reference, such as $B5 or B$5. Depending on the type of relative reference, either the column or row reference changes while the other remains constant when you copy the formula.
Avoid circular references.
A circular reference occurs when a formula refers to the cell containing the formula. The status bar indicates the location of a circular reference. You should correct circular references to prevent inaccurate results.
Insert a function.
A function is a predefined formula that performs a calculation. It contains the function name and arguments. Formula AutoComplete, function ScreenTips, and the Insert Function dialog box help you select and create functions. The Function Arguments dialog box guides you through entering requirements for each argument.
Total values with the SUM function.
The SUM function calculates the total of a range of values. The syntax is =SUM(number1,[number2],…) where the arguments are cell references to one or more ranges.
Insert basic statistical functions.
The AVERAGE function calculates the arithmetic mean of values in a range. The MEDIAN function identifies the midpoint value in a set of values. The MIN function identifies the lowest value in a range, whereas the MAX function identifies the highest value in a range. The COUNT function tallies the number of cells in a range, whereas the COUNTBLANK function tallies the number of blank cells in a range. Excel contains other math and statistical functions, such as FREQUENCY and MODE.
Use date functions.
The TODAY function displays the current date, and the NOW function displays the current date and time. Other date functions identify a particular day of the week, identify the number of net working days between two dates, and display a serial number representing a date.
Determine results with the IF function.
The IF function is a logical function that evaluates a logical test using logical operators, such as , and =, and returns one value if the condition is true and another value if the condition is false. The value_if_true and value_if_false arguments can contain cell references, text, or calculations. You can nest or embed other functions inside one or more of the arguments of an IF function to create more complex formulas.
Use lookup functions.
The VLOOKUP function looks up a value for a particular record, compares it to a lookup table, and returns a result in another column of the lookup table. Design the lookup table using exact values or the breakpoints for ranges. If an exact match is required, the optional fourth argument should be FALSE; otherwise, the fourth argument can remain empty. The HLOOKUP function looks up values by row (horizontally) rather than by column (vertically).
Calculate payments with the PMT function.
The PMT function calculates periodic payments for a loan with a fixed interest rate and a fixed term. The PMT function requires the periodic interest rate, the total number of payment periods, and the original value of the loan. You can use the PMT function to calculate monthly car or mortgage payments.
Create and maintain range names.
A range name is a descriptive name that corresponds with one or more cells. A range name may contain letters, numbers, and underscores, but must start with either a letter or an underscore. The quick way to create a range name is to select the range, type the name in the Name Box, and then press Enter. Use the Name Manager dialog box to edit, create, or delete range names. You can insert a list of range names on a worksheet.
Use range names in formulas.
You can use range names in formulas instead of cell references. Range names are absolute and can make your formula easier to interpret by using a descriptive name for the value(s) contained in a cell or range.
