Microsoft Excel Advanced Formulas and Macros 2 — Questions and Answers
Question 1: What is the purpose of IFERROR in Excel?
- Checks if a formula contains IF
- Returns a specified value if a formula evaluates to an error (Correct answer)
- Logs errors to a worksheet
- Prevents incorrect formula entry
Correct 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.
IFERROR(value, value_if_error) catches all error types. For specific handling, use IFNA (catches only #N/A) or ERROR.TYPE.
Question 2: Which VBA statement redirects execution when an error occurs?
- Try...Catch
- On Error GoTo (Correct answer)
- Error.Handle
- Catch Exception
Correct answer: On Error GoTo
On Error GoTo directs VBA to jump to a labeled line when a runtime error occurs.
Three forms: On Error GoTo Label (jump to handler), On Error Resume Next (ignore), On Error GoTo 0 (reset to default).
Question 3: What does TEXTJOIN do that CONCATENATE cannot?
- Joins text with a delimiter and can ignore empty cells (Correct answer)
- Joins text with formatting
- Converts numbers to text first
- Joins across workbooks
Correct answer: Joins text with a delimiter and can ignore empty cells
TEXTJOIN joins text using a specified delimiter and can skip empty cells.
TEXTJOIN(delimiter, ignore_empty, text1, ...) accepts ranges as arguments and allows specifying a delimiter once.
Question 4: In VBA, what is the difference between 'ByRef' and 'ByVal'?
- ByRef passes a copy, ByVal passes a reference
- ByRef passes the reference allowing modification, ByVal passes a copy (Correct answer)
- No difference in VBA
- ByRef for objects only, ByVal for primitives
Correct 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.
Best practice is ByVal unless you intentionally want to modify the caller's variable, preventing accidental side effects.
Question 5: What is SUMPRODUCT commonly used for beyond multiplication?
- Only multiplying array elements
- Conditional counting and summing with multiple criteria without Ctrl+Shift+Enter (Correct answer)
- Calculating matrix determinants
- Finding the product of a range
Correct 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.
Boolean conditions return 1/0 arrays that act as filters. Unlike SUMIFS, SUMPRODUCT handles OR conditions and complex logic.
Question 6: How do you create a named range that automatically expands?
- Use Define Name with a fixed range
- Use OFFSET and COUNTA in the name definition (Correct answer)
- Set the range to 'Dynamic' in Name Manager
- Named ranges cannot expand
Correct answer: Use OFFSET and COUNTA in the name definition
Combining OFFSET with COUNTA creates a dynamic named range that grows with your data.
Classic formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). Tables or INDEX alternatives are preferred as OFFSET is volatile.
Microsoft Excel Advanced Formulas and Macros 2
What is the purpose of IFERROR in Excel?