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 AGGREGATE's advantage over SUM and AVERAGE?
Answer: It can ignore errors, hidden rows, and nested SUBTOTAL results
AGGREGATE performs 19 different functions while optionally ignoring error values, hidden rows, and nested functions.
In VBA, when would you use a Collection over an Array?
Answer: Collections dynamically grow, support keys, and don't need dimension management
Collections grow automatically, support string key lookups, and don't require managing dimensions.
What does the SWITCH function do?
Answer: Evaluates an expression against a list of values and returns the first match
SWITCH compares an expression against value-result pairs, returning the result for the first match.
How does CHOOSECOLS work in Excel 365?
Answer: Returns specific columns from an array by column number
CHOOSECOLS extracts specific columns from an array by their column numbers.
What does Application.ScreenUpdating control in VBA?
Answer: Screen refresh during macro execution for performance
Setting it to False prevents screen redrawing during macro execution, speeding up macros significantly.
What does the MAP function do in Excel 365?
Answer: Applies a LAMBDA function to each array element
MAP applies a LAMBDA to every element in one or more arrays and returns the results.