Advanced Formulas and Macros Flashcards
6 cards from real Microsoft Excel practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 Advanced Formulas and Macros flashcards as text
What is the purpose of IFERROR in Excel?
Answer: Returns a specified value if a formula evaluates to an error
IFERROR returns an alternative value if the formula results in any error type.
Which VBA statement redirects execution when an error occurs?
Answer: On Error GoTo
On Error GoTo directs VBA to jump to a labeled line when a runtime error occurs.
What does TEXTJOIN do that CONCATENATE cannot?
Answer: Joins text with a delimiter and can ignore empty cells
TEXTJOIN joins text using a specified delimiter and can skip empty cells.
In VBA, what is the difference between 'ByRef' and 'ByVal'?
Answer: ByRef passes the reference allowing modification, ByVal passes a copy
ByRef (default) passes a reference so changes affect the original; ByVal passes a copy.
What is SUMPRODUCT commonly used for beyond multiplication?
Answer: Conditional counting and summing with multiple criteria without Ctrl+Shift+Enter
SUMPRODUCT evaluates multiple Boolean conditions in arrays and sums results without CSE entry.
How do you create a named range that automatically expands?
Answer: Use OFFSET and COUNTA in the name definition
Combining OFFSET with COUNTA creates a dynamic named range that grows with your data.