Exam 2

Excel Database

-Range of cells that includes header row and rows of data. -Stores data made up of records (rows) and fields (columns)

XLOOKUP Match Modes

0 = exact match, if none found, #N/A is displayed (default) -1 = exact match, if none found the next smaller item is displayed 1 = exact match, if none found the next larger item is displayed 2 = a wildcard match using *, ?, ~

type (pv function)

1 - payment at the beginning of the period 0 - payment at the end of the period (default)

Match Type Values

1 - returns a match for the largest value that is less than or equal to the lookup value 0 - returns exact match -1 - returns the smallest number that is greater than or equal to the lookup value

XLOOKUP Search Modes

1 = perform a search starting at the first item (default) -1 = perform a reverse search starting at the last item 2 = perform a binary search, lookup array must be sorted in ascending order -2 = perform a binary search, lookup array must be sorted in descending order

AND Syntax

=AND(logical1, [logical2]..)

CONCAT Syntax

=CONCAT(text1, text2,...)

CUMIPMT Syntax

=CUMIPMT(rate, nper, pv, start_period, end_period)

CUMPRINC Syntax (does not allow negative arguments)

=CUMPRINC(rate,nper, pv, start_period, end_period)

FILTER Syntax

=FILTER(array, include, [if_empty])

FV Syntax

=FV(rate, nper, pmt, [pv], [type])

IFERROR Syntax

=IFERROR(value, value_if_error)

IFS Syntax

=IFS(logical_test1, value_if_true,[logical_test2], [value_if_true2]...)

INDEX Syntax

=INDEX(array, row_num, [col_num])

IPMT Syntax

=IPMT(rate, per, nper, pv, [fv], [type])

MATCH Syntax

=MATCH(lookup_value, lookup_array, [match_type])

NPV Function Syntax

=NPV(rate, value1, value 2, ...)

OFFSET Syntax

=OFFSET(reference, rows, cols, [height], [width])

PPMT Syntax

=PPMT(rate, per, nper, pv, [fv], [type])

PV Syntax

=PV(rate, nper, pmt, [fv], [type])

RATE Syntax

=RATE(nper, pmt, pv, [fv], [type], [guess])

REPLACE Syntax

=REPLACE(old_text, start_num,num_chars, new_text)

SORT Syntax

=SORT(array, [sort_index],[sort_order], [by_col])

SUBTOTAL Syntax

=SUBTOTAL(function_num, ref1,[ref2])

SWITCH Syntax (126 matching values and results can be evaluated)

=SWITCH(expression, value1, result1, default_or_value2, result2 ...)

TEXT Syntax

=TEXT(value, format_tex)

TEXTJOIN Syntax

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

XLOOKUP Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found), [match_mode], [search_mode])

Database Function Syntax

=function(database, field, criteria)

CUMIPMT Function

Calculates cumulative interest for a specified payment period.

TEXT Function

Can display numeric data as text using special formatting strings to display the text

Common Database Functions

DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DSUM

Form Control

Object that can be used to display a choice, to run a command, or to perform an action in a worksheet

Database Functions

Perform calculations on data that meet criteria

Error Alert

Pop up message that appears after invalid data is entered

rows (offset)

The number of rows, up or down, that you want the upper-left cell to refer to, positive rows means below and negative rows means above the starting reference.

FILTER Function

a dynamic array function that filters an array based on a Boolean (T/F) array

SORT Function

a dynamic array function that returns a sorted array of the elements in an array, automatically updates if the source data is changed

by_col

a logical value indicating the desired sort direction (false to sort by row, true to sort by column)

sort_order

a number indicating the desired sort order (1 is ascending, -1 is descending)

sort_index

a number indicating the row or column to sort by, default is row 1 column 1

Excel Template

a prototype or sample workbook, that provides a starting point for building other similar workbooks (.xtlx)

Smart Lookup

a research feature that uses bing to find information about a label or a value

Array Constants

a set of static values, text can be used

Array Formula

a shortcut or more efficient way of performing certain complex calculations

Types of Validation Criteria

any value, whole number, decimal, list, date, time ,text length, custom

Input Message

appears when a user makes a validated cell active, comment box that contains a guideline for the user entering the data

Form Control Examples

button, list box, combo box, option button, check box, spin button, scroll bar

PPMT Function

calculates the amount of a loan payment applied to the principal

FV Function

calculates the future value of a loan or an investment

NPER Function

calculates the number of payments required to repay a loan or reach an investment goal

RATE Function

calculates the per-period interest rate required to repay a loan or reach an investment goal

PV Function

calculates the present value of a loan or an investment

Disadvantages of Array Formulas

can be difficult to understand, if complex they can slow down calculations

TRANSPOSE Function

changes the rows of a selected range into columns and vice versa (=TRANSPOSE(array))

TEXTJOIN Function

combines strings of text, values, or characters

Comments vs. Notes

comments allow replies, notes are just annotations

EXACT Function

compares two text strings, values, or characters to determine if they are identical (=EXACT(text1,text2))

Advantages of Array Formulas

consistency, safety, smaller file sizes

Form Button

creates a screen form for data entry

AND Function

defines multiple conditions that must be med for a true result

IFERROR Function

detects an error and displays a more user friendly message

INDEX Function

displays the contents of the cell at the intersection of a specified column and row

SWITCH Function

evaluates an expression against a list of values and returns the result corresponding to the first matching value, if there is no match, an optional default value is returned (does NOT accept logical operators <,>,=)

Dynamic Range Names

expand automatically when a value is added to the range

Dynamic Array Formulas

formulas/functions that can return arrays of variable size

IFS Function

have one or more logical tests, the result that displays in the cell represents the first logical test that is true

IPMT Function

how much of a specific periodic payment is going toward the interest that has accrued on the loan

Check Accessibility

identifies content that a person with visual or literacy impairments might find difficult to understand

NOT Function

if one of the arguments is true, displays false, if the arguments are false, display true

OR Function

if one of the arguments is true, the result is true, all arguments must be false to display false

Problems with Data Validation

if you add validation after the data is already entered, Excel does not identify the problems

Format Control Dialog

includes properties and settings specific to each type of control

Information Error Message

invalid entry is allowed, but Excel warns the user that they are breaking a rule

Warning Error Message

invalid entry is allowed, but can be edited or canceled after a warning

CONCAT Function

joins or combines data strings, does not have the delimiter argument

Match Function

looks for a value within a range and returns the position of that value within the range

SORT(FILTER)

order to use when you want to sort filtered data

SUBTOTAL Function

performs calculations on data in a filtered subset

Workbook Level Protection

protects workbook structure, number and arrangement of worksheets, does not protect the contents of cells

Worksheet Level Protection

protects worksheets from alteration

Alt Text

read aloud by a screen reader to aid those who need assistance

REPLACE Function

replaces part of a text string, based on the number of characters specified, with a different text string

Spilling

returning a set of values to neighboring cells

OFFSET Function

returns a reference to a range that is a specified number of rows and columns from a cell or range of cells

Validation Settings

rules applied to data as it is entered

Codification Schemes

rules that combine data values in specific formats and locations to generate a new data value (used to create a transaction code or id)

XLOOKUP Function

searches a range or an array and returns an item corresponding to the first match it finds, if a match does not exist, it can return the closest match

Two Dimensional Array

separate items in each row with commas and delimit each column with a semicolon

Horizontal Array Constant (a row)

separate the values with a comma, uses opening and closing "{}"

Vertical Array Constant ( a column)

separate values using a semicolon, type opening and closing "{}"

pmt

the amount of each payment

FV

the amount of money left after the last payment is made

reference (offset)

the reference from which you want to base the offset, must refer to a cell or range of adjacent cells

Net Present Value

the value today of an investment, allows for variable payment amounts

nper

total number of payments

How to Create Dynamic Range Names

use OFFSET and COUNTA, (cannot use the name box)

CUMPRINC Function

used to calculate the amount of principal paid over a specific number of periods, such as quarterly or annually.

Watch Window

used to monitor results in different parts of a large worksheet or workbook

Stop Error Message

user cannot enter the data, and must retry to fit the data validation