ISBA Chapter 2 Excel Notes

Edit formulas using

Range finder and formula bar.

Mathmatical order of predence

Parentheses  Exponent (^),  multiplication and division Addition and subtraction,

Mathematical order of precedence acronym

Please  Excuse  My  Dear  Aunt  Sally

Multiple operations in a formula

Are parentheses necessary for proper evaluation? – Equal in priority – Left to right. Different priority – Order, then Left to Right.

Rules for naming a cell range

Begin a range name with a letter. Use a short, descriptive name. Do not use spaces or special characters in a range name. Separate words in a range name with an underscore as in “First_Qtr” or use initial caps for each word such as “FirstQtr.” Do not name a range with a single character such as “N.” Do not name a range with a cell reference such as “B2.”

Trace Precedents

Displays lines with arrows to identify all cells referenced in the formula in the active cell.

Trace dependents

Display lines with arrows to all cells that use the active cell directly or indirectly in a formula.

Remove arrows

Removes all lines and arrows from the Trace Precedents or Trace Dependent buttons

Error checking

Checks data against the error rules in Excel Options.

Evaluate Formula

Steps through each part of a formula and displays an outcome for each part so that an error can be isolated.

Watch window

Opens a floating window that displays selected cells and values for monitoring.

Common functions

Average, count, max and min, autocalculate, today and now.

PMT Function

Calculates loan payments. For personal and business uses. Assumes the borrower makes regular payments and the loan has a constant interest rate.

Rate

Interest rate per period.

Nper

Total number of periods for repayment

Pv

Present value (principal)

Fv

Future value

Type

When payments are due (optional)

IF function

Evaluates a specified condition. Returns one value if the condition is true and one if the condition is false.

IF Function’s three arguments

Logical test, value if true, and value if false.

LOOKUP Functions

VLOOKUP and HLOOKUP

Math and Trigonometry Functions

ROUND, SUMIF, and SUMPRODUCT

Formula

An expression or statement that uses arithmetic operations to perform a calculation.

Basic arithmetic operations

Addition, subtraction, multiplication, and division

How to use multiple operations in a formula

1. Type = to start the formula. 2. Type ( to start the calculation that should have priority. 3. Click the first cell for the calculation to be enclosed in parentheses. 4. Type the arithmetic operator. 5. Click the next cell for the calculation to be enclosed in parentheses. 6. Type ) to end the calculation with priority. 7. Complete the formula. 8. Press enter.

Relative cell formula

The location of a cell, such as cell B2. In column B, the formula is =B5+B7. But when copied to column C, it becomes =C5+C7.

Absolute cell reference

Indicated with dollar signs as in cell $B$2. When an ____ is copied, it does not change. In the formula =$B$6 – C7, $B$6 would not change when copied anywhere in the workbook.

Mixed cell reference

Have one relative and one absolute reference. Cell $B2 is a ___. When it is copied, it always refers to column B, but the row number updates to the row where the copy is located.

3D cell reference

A cell located in another worksheet in the same workbook. It can be absolute, mixed or relative. Includes the name of the sheet as in Sheet2!B2. The sheet name is followed by an exclamation point.

How to name a range and use formulaautocomplete

1. Select the cell or range to be named. 2. Click the name box to the left of the formula bar. 3. Type the name and press enter. 4. Click the cell for the formula. 5. Type = to start the formula. 6. Type the first one or two either of the range name. 7. Find the range name and double-click it. 8. Complete the formula. 9. Press enter when the formula is complete.

How to define and scope a range name

1. Select the cell range to be named. 2. Click the define name button. 3. Type the range name in the name box. 4. Click the scope drop-down list. 5. Select or red-edit the refers to entry. 6. Click OK.

How to paste a range name in a formula and paste a list

1. Click the formula and type +. 2. Press F3 to open the paste name dialog box. 3. Find the range name and double-click it. 4. Complete the formula and press enter. 5. Click a blank cell in an unused area of the worksheet. 6. Click the use in formula button. 7. Select paste names at the bottom of the list. 8. Click paste list.

Formula auditing

The process of reviewing formulas for accuracy. Excel automatically audits formulas as you enter them and when you open a workbook based on its own error checking rules. Excel recognizes special types of errors.

How to trace an error

1. Click the cell with the triangle error. 2. Point to the trace error button to display a screentip. 3. Click the trace error drop-down list. 4. Choose an option for the error.

Circular reference

A ____ error occurs when a formula includes the cell address of the formula.

Circular reference error example

If the formula in cell B10 is =B5+B10, the reference Is ____. When you try to complete such a formula, a message box opens but Excel does not correct the error or prevent you from keeping it in the worksheet with inaccurate results.

Central tendency

Statistical measurement that reflects the average, the middle, or the most common.

Serial number

A unique value assigned to each date. Excel starts by setting January 1, 1900, as number 1; January 2, 1900 is number 2, and so on. January 1, 2022, is number 44562.

Volatile

Means that the result depends on the current date, time, and computer.

Volatile function examples

TODAY and NOQ function.

PMT function’s five arguments

3 required – Rate, nper, and p. 2 optional – FV and type.

Proper syntax for a PMT function

=PMT(rate, nper, pv, [fv], [type])

PMT function example

=PMT(B6/12, B5*12, B4,,1). B6/12 is the interest rate divided by 12 to determine a monthly payment. B5*12 is the nper argument, the number of years times 12, to determine the total number of payments. B4 is the pv argument, the amount of money borrowed. 1 is the type argument and indicates that payment occurs at the beginning of the period.

IF’s syntax

=IF (logical_test, value_if_true, value_if_false_

Logical_test

The value or statement to be evaluated

Value_if_true

The result displayed in the cell when the logical_ test is true. You can select a cell, enter text, or use a formula for this argument.

Value_if_false

The result displayed in the cell when the logical_test is false. You can select a cell, enter text, or use a formula for this argument.

Literal

A value or label that should display exactly as shown between the quotation marks. They are constant and do not change when the formula is copied. The word “yes” in a value_if_true argument.

LOOKUP function

Displays a piece of data from an existing list in the workbook.

Array

A collection of values. An ___ in a worksheet is typically a cell range.

XLOOKUP function’s six arguments

3 required – lookup_value, lookup_array, and return_array. 3 optional – If not found, match mode, and search mode.

Lookup_value

The data to be found or matched.

Lookup_array

The range of cells to be searched.

Return_array

The range in which the result is matched. This range contains the data to display in the result.

[If_not_found} argument

Results when no match is found in the lookup_array for the lookup_value. The standard default message is #N/A but you can type a custom response such as “No such number.”

[Match_mode]

Determines if the function looks for an exact match or an approximate match. The default is an exact match.

[Search_mode]

The option to specify if the search is first to last or last to first.

[Match_mode] argument 0

Exact match. If not found, display #N/A. This is the default

[Match_mode] argument -1

Exact match. If not found, display the next smaller result.

[Match_mode] arguments 1

Exact match. If not found, display the next larger result.

[Match_mode] argument 2

Wildcard match using *, ?,

[Search_mode] argument 1

Start searching at first item. This is the default

[Search_mode] argument -1

Start searching at last item.

[Search_mode] argument 2

Binary search that requires look_up array to be sorted in ascending order.

[Search_mode] argument -2

Binary search that requires look_up array to be sorted in descending order.

VLOOKUP function

Three required arguments – Lookup_value, table_array, col_index_num. Optional – [range lookup].

VLOOKUP syntax

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]).

Table_array

A range of cells, sorted in ascending order by the first column.

Col_index_num

Sets which column in the table_array contains the data to display in the result. The columns are counted from left to right.

[Range lookup] argument

Either TRUE or FALSE. When you set this argument to FALSE, Excel finds an exact match and the data can be in any order. TRUE means that Excel finds thee closest match to the lookup_value, which can return unexpected results.

VLOOKUP function example

=VLOOKUP(A4, Rates!$J$4

HLOOKUP syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Row_index_num

Sets which row in the table_array contains the data to display in the result. The rows are counted from top to bottom.

HLOOKUP example

=HLOOKUP(C5,$O$3

TEXTJOIN function

Combines strings of text, values, or characters. Three required arguments – Delimiter, ignore_empty, text1. One optional argument – Text2.

TEXTJOIN function syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2],…)

Delimiter

The ___ argument is the character used to separate the textN arguments. This character is inserted after each textN argument except the last one. You can have up to 252 textN arguments but only one ___. A text argument is a cell reference or data that you type.

CONCAT

Joins or combines data strings. Does not have a delimiter argument.

CONCAT function syntax

=CONCAT(text1,[text2], …)

Rounding

A value is adjusted to display a specified number of decimal places or a whole nu mber.

ROUND function syntax

=ROUND(number, num_digits)

Num_digits

Sets the number of decimal places for rounding. When this argument is zero (0), the value displays as the nearest whole number. When the ___ argument is greater than zero, the value is rounded to that number of decimal places. You can use a negative number for the ___ argument, which rounds the value to the left of the decimal point. Make sure you understand mathematical results when you round to the left of the decimal point.

SUMIF function

Combines a sum with an If statement to limit which data are included in the total. It defines which cells are part of the calculation by criteria

SUMIF FUNCTION arguments

Two required arguments – Range and criteria. One optional argument – [Sum_range].

SUMIF function syntax

=SUMIF (range, criteria, [sum_range])

Range

The ____ of cells to be evaluated or searched, the values that are compared to the criteria.

Criteria

The ___ argument defines which cells should be included in the sum. You can use comparison operations, cell references, a value, or text.

[Sum_Range]

The cell range to be summed. Although it’s optional, it’s necessary when the range argument and the values to be summed are different. It can be omitted when the range to be summed is the same as the range argument.

SUMIF example

=SUMIF ($B$4

SUMPRODUCT function

Calculates the sum of the product of several ranges. It multiplies the cells identified in its array arguments and then totals those individual products.

Product

The result of a multiplication problem.

Dimension

The number of columns or rows.

Array

A collection of values in a row, a column, or both, basically a range. An ___has a dimension.

SUMPRODUCT formula syntax

=SUMPRODUCT(array1, array2, [arrayN]).