Excel

Question 1

Operator precedence

Answer 1

Within parentheses first; left to right with * and / before + and -

2016: 210-211. 2019: 209-210

Question 2

Strategy for developing a long formula

Answer 2

Create a piece at a time, verifying results of each piece before proceeding

Question 3

Function SMALL (range, k)

Function LARGE (range, k)

Answer 3

Smallest value in a range: SMALL (range, 1)

2016: 414-415. 2019: 417-418

Question 4

Function COUNT (range)

Answer 4

Count nonblank entries in a range

2016: 302. 2019: 250-251

Question 5

Replace blank cells with a value and color fill

Answer 5

[Find&Select] -> [GoToSpecial] -> [Blanks] -> select color -> enter value -> [Ctrl][Enter]

Question 6

Insert column to left

Answer 6

Highlight column, right-click -> [Insert]

2016 & 2019: 66-67

Question 7

Rearrange columns

Answer 7

Click column header of column to move. In Home tab, [Clipboard], right-click column header of column to move to, right-click the column header of column to move -> Cut. Click column header to right of where column is to go. Right-click and select [Insert Cut Cells] from shortcut menu

2016: 751. 2019: 751

Question 8

Average a range omitting smallest value

Answer 8

=(SUM(range)-SMALL(range,1))/ (COUNT(range)-1)

Question 9

Rescale values, e.g., from 5 to 10 or from 15 to 10

Answer 9

Multiply original by the multiplier (whole number or fraction), e.g., for 5 to 10, multiply by 2, for 15 to 10, multiply by 2/3

Question 10

Absolute and relative references

Answer 10

Relative reference: A8
Absolute column only: $A8
Absolute row only: A$
Absolute column & row: $A$8
Press [F4] to cycle choices

2016: 222-225. 2019: 221-223

Question 11

Copy/propagate formula to bottom of range

Answer 11

Highlight formula cell(s) and move cursor to lower right corner. When cursor changes to a bold +, double-left click.

Question 12

Shortcut: Display format Cells

Answer 12

[Ctrl]1

Question 13

Hardcoding (sure way to make errors!)

Answer 13

Typing values rather than using references to cells containing parameters

2016 & 2019: 241, 824-825

Question 14

Parameters

Answer 14

Values that can vary over time, i.e., interest rates, weights. Using formula references to these values is parameterization.

2016 & 2019: 824-825

Question 15

Built-in functions

Answer 15

Examples: average, minimum, maximum, count, small, IF

2016 & 2019: 212-214, 219-221, 1033-1051

Question 16

Excel: IF function

Access: IIF function

Answer 16

IF(logicalTest, [valueIfTrue], [valueIfFalse])

When logicalTest returns a value of TRUE, [valueIfTrue] goes in the cell. When logicalTest returns a value of FALSE, [valueIfFalse] goes in the cell.

2016: 329. 2019: 295-296

Question 17

Nested IFs and IIFs

Answer 17

In an IF/IIF function, [valueIfTrue] and [valueIfFalse] arguments can be IF/IIF functions (64 nested levels for Excel; unlimited for Access)

Question 18

VLOOKUP Function

Answer 18

VLOOKUP(lookupValue, tableArray, ColIndexNum, rangeLookup)
rangeLookup = FALSE for exact match
rangeLookup = TRUE or omitted for approximate match

Excel 2016: 330-332. Excel 2019: 316-318, 323-324

Question 19

VLOOKUP table format for [tableArray] argument

Answer 19

First column: Values sorted ascending for looking up
Columns after first: Values to extract

Question 20

Change column width

Answer 20

Place and hold cursor on right border of column header and drag border to desired column width.