Microsoft Excel Advanced Formulas and Macros Questions and Answers 1 — Questions and Answers
Question 1: An analyst needs to create a dynamic, spillable list of all sales records from a table named 'SalesData' where the '[Region]' is "East" AND the '[Revenue]' is greater than 10000. Which of the following formulas is the most appropriate for this task?
- =FILTER(SalesData, (SalesData[Region]="East") * (SalesData[Revenue]>10000)) (Correct answer)
- =SUMIFS(SalesData[Revenue], SalesData[Region], "East", SalesData[Revenue], ">10000")
- =IF(AND(SalesData[Region]="East", SalesData[Revenue]>10000), SalesData, "No Results")
- =VLOOKUP("East", SalesData, 3, FALSE)
Correct answer: =FILTER(SalesData, (SalesData[Region]="East") * (SalesData[Revenue]>10000))
The FILTER function is specifically designed to return a range of data that meets a set of criteria. To apply multiple 'AND' conditions, the logical tests for each condition are multiplied together.
Question 2: A user is writing a complex formula that references the same calculated result multiple times, making the formula long and inefficient. Which function allows the user to assign a name to a calculation result and reuse that name within the formula to improve readability and performance?
- LAMBDA
- IFERROR
- LET (Correct answer)
- SUBSTITUTE
Correct answer: LET
The LET function assigns names to calculation results, allowing you to store an intermediate calculation and reuse it multiple times within the formula. This improves performance by calculating the result only once and makes the formula easier to read.
Question 3: To perform a two-way lookup, finding a value at the intersection of a specific row and column in a data grid (B2:F10), what is the correct syntax using INDEX and MATCH? Assume the row lookup value is in H1 (searching A2:A10) and the column lookup value is in I1 (searching B1:F1).
- =INDEX(B2:F10, MATCH(H1, B1:F1, 0), MATCH(I1, A2:A10, 0))
- =INDEX(B2:F10, MATCH(H1, A2:A10, 0), MATCH(I1, B1:F1, 0)) (Correct answer)
- =VLOOKUP(H1, A1:F10, MATCH(I1, B1:F1, 0), FALSE)
- =HLOOKUP(I1, A1:F10, MATCH(H1, A2:A10, 0), FALSE)
Correct answer: =INDEX(B2:F10, MATCH(H1, A2:A10, 0), MATCH(I1, B1:F1, 0))
The correct structure is INDEX(data_range, row_number, column_number). The first MATCH(H1, A2:A10, 0) finds the correct row number based on the vertical list. The second MATCH(I1, B1:F1, 0) finds the correct column number based on the horizontal list.
Question 4: A developer is recording a macro that needs to perform a series of formatting steps starting from any active cell. To ensure the macro's actions are recorded based on their position relative to the starting cell (e.g., move one cell right), which setting must be enabled before performing the actions?
- Enable All Macros in Trust Center
- The Personal Macro Workbook
- Stop Recording
- Use Relative References (Correct answer)
Correct answer: Use Relative References
By default, Excel records macros with absolute references (e.g., Range("C5").Select). Enabling 'Use Relative References' records actions based on their offset from the active cell, allowing the macro to be run from any starting point on the worksheet with the same relative effect.
Question 5: A company policy requires that all macros in workbooks must be disabled by default, but users should be notified with a security warning and given the option to enable them if the workbook is from a trusted source. Which Macro Security setting in the Trust Center achieves this balance?
- Enable all macros (not recommended)
- Disable all macros without notification
- Disable all macros with notification (Correct answer)
- Disable all macros except digitally signed macros
Correct answer: Disable all macros with notification
The 'Disable all macros with notification' setting is the default and most common security level. It prevents macros from running automatically but provides a message bar, allowing the user to enable content on a case-by-case basis.
Question 6: In the formula `=XLOOKUP(A1, B:B, C:C, "Not Found", 0, -1)`, what is the purpose of the final argument, `-1`?
- It forces the formula to find the next smallest item if an exact match is not found.
- It instructs the function to perform a search from the last item to the first. (Correct answer)
- It indicates that the lookup array is sorted in descending order for a binary search.
- It specifies that a wildcard match should be performed.
Correct answer: It instructs the function to perform a search from the last item to the first.
The sixth argument of the XLOOKUP function is [search_mode]. A value of 1 (the default) searches from first-to-last. A value of -1 reverses the search direction, starting from the last item and moving to the first, which is useful for finding the last occurrence of a value.
Microsoft Excel Advanced Formulas and Macros Questions and Answers 1
An analyst needs to create a dynamic, spillable list of all sales records from a table named 'SalesData' where the '[Region]' is "East" AND the '[Revenue]' is greater than 10000.
Which of the following formulas is the most appropriate for this task?