excel midterm

=ADDRESS( ??? )

row, column, [absolute / relative cell reference format]

• 1 → absolute reference ($C$5)

• 2 → absolute row, relative column (C$5)

• 3 → relative row, absolute column ($C5)

• 4 → relative reference (C5)

what does ADDRESS return

returns cell address in text format

=INDIRECT ( ??? )

text in cell reference form

what does INDIRECT return

returns what the cell it’s referencing references

=ROW(cell reference)

returns row

=COLUMN(cell reference)

returns column

=INDEX( ??? )

cell reference range, row, column

what does INDEX return

returns cell value of target cell y rows down and x columns right of cell range

=OFFSET( ??? )

cell reference, rows, columns, height, width

=IF( ??? )

logical comparison, return value if TRUE, return value if FALSE

=IFS( ??? )

logical comparison 1, return value if its TRUE, logical comparison 2, return value if its TRUE, etc.

=SWITCH( ??? )

target value cell, match value 1, replace value 1, match value 2, replace value 2, etc.

=COUNT( ??? )

value / cell reference / cell reference range

what’s the difference between COUNT and COUNTA

COUNT only returns the number of cells that contain numbers

COUNTA returns the number of cells that include everything (is not empty)

how does AVERAGEA deal with non-numeric input

indirectly inputted text as 0

false as 0

true as 1

=LARGE( ??? ) / =SMALL( ??? )

cell reference range, number / k

what does LARGE return

returns the k-th largest number

=RANK( ??? )

number, cell reference range to list of numbers, [0 / non-0]

• 0 → descending

• non-0 → ascending

=ROUND( ??? )

value / cell reference, number (k)

what’s the difference between ROUND and MROUND

ROUND rounds to the k-th digit after the decimal point

MROUND rounds to the nearest multiple of k

=RANDBETWEEN( ??? )

value / cell reference / x, value / cell reference / y

• lower boundary (x) rounds larger

• upper boundary (y) rounds smaller

  • will return the lower boundary if y > x

text, number, and logical value — what’s the greatest

logical value (true > false) > text > number

=COUNTIF( ??? )

cell reference range, criteria (in text format)

*

wildcard → represents one or more consecutive text characters

?

represents a single character

~ *

~ returns the actual character

how does COUNTIF convert data

into most likely data

• e.g., “=TRUE” into TRUE (logical value) — will not match TRUE (text)

=COUNTIFS( ??? )

cell reference range 1, criteria 1, cell reference range 2, criteria 2, etc

=SUMIF( ??? )

cell reference range, criteria, sum range

=SEARCH( ??? )

text, value / cell reference

what does SEARCH return

returns the location within the value / cell reference

=VLOOKUP( ??? )

lookup value, data range, return column number, match method

• FALSE → exact match

• TRUE → approximate match

=LOOKUP( ??? )

lookup value, single row / column range for lookup value, single row / column for return value

=XLOOKUP( ??? )

lookup value, single row / column for lookup value, single row / column for return value, [return value if no matches], [match method], search method]

• 4 match methods (exact, almost exact, wildcards)

• 4 search methods (start from first / last cell or binary search)

=MATCH( ??? )

lookup value, data range, match method

• data range is single row / column

• 3 match methods

match and index

INDEX(range, MATCH(…), 1)

=CHOOSE( ??? )

index number k, value / cell reference / cell reference range 1, etc.

what does CHOOSE return

return the k-th value / cell reference / cell reference range