Module 3 Test: Advanced Formulas, Analysis & Macros

Course: MO-211: Microsoft Excel Expert Topics covered: 3.1 Logical Functions · 3.2 Lookup · 3.3 Date & Time · 3.4 What-If Analysis · 3.5 Arrays · 3.6 Financial Functions · 3.7 Troubleshooting · 3.8 Macros · 3.9 Consolidate · 3.10 Forecast Sheet

This test combines exam-style multiple choice with hands-on micro-tasks. Try every question with your notes closed first; reveal the answer key only after attempting all of them. Practice file: practice/module-3-finance-and-formulas.xlsx.

Section A: Lesson 3.1 — Logical Functions

Q1. Logical Branching

You need to assign a letter grade based on a numeric score: >= 90 → "A", >= 80 → "B", >= 70 → "C", otherwise "F". Which function gives the cleanest, flattest formula? - A) Nested IF statements four levels deep. - B) SWITCH(score, 90, "A", 80, "B", 70, "C", "F"). - C) IFS(score>=90,"A", score>=80,"B", score>=70,"C", TRUE,"F"). - D) LOOKUP(score, {0;70;80;90}, {"F";"C";"B";"A"}) — but IFS is cleaner.

Q2. SWITCH vs IFS

A worksheet has shipping codes 1, 2, 3 that map to "Standard", "Express", "Overnight". Which function is the most natural fit? - A) IFS with three conditions. - B) SWITCH(code, 1, "Standard", 2, "Express", 3, "Overnight"). - C) IF(code=1, "Standard", IF(code=2, "Express", "Overnight")). - D) CHOOSE(code, "Standard", "Express", "Overnight") — both SWITCH and CHOOSE work, but SWITCH is more readable.

Q3. Conditional Sum with Two Criteria

You want the total of the Amount column for rows where Region = "East" AND Status = "Paid". Which formula is correct? - A) =SUMIF(Amount, "East", Region) + SUMIF(Amount, "Paid", Status) - B) =SUMIFS(Amount, Region, "East", Status, "Paid") - C) =SUMIF(Amount, Region="East" AND Status="Paid") - D) =SUM(IF(Region="East", IF(Status="Paid", Amount)))

Q4. LET — Why Use It

What is the primary benefit of wrapping a long formula in LET()? - A) It enables iterative calculation automatically. - B) It computes a sub-expression once, names it, and reuses the named value — improving both speed and readability. - C) It is required for any formula with more than five arguments. - D) It converts the formula to a dynamic array.

Section B: Lesson 3.2 — Lookup Functions

Q5. VLOOKUP Mismatch

A VLOOKUP returns #N/A for some rows and correct values for others. The lookup column is sorted, the table_array is correct, and the lookup values appear identical to the values in the table. What is the most likely cause? - A) The fourth argument is TRUE instead of FALSE. - B) Hidden whitespace or a trailing space in the lookup or table values. - C) The table has a header row. - D) VLOOKUP only works on numeric data.

Q6. XLOOKUP — match_mode for Tier Lookup

You want XLOOKUP to find the discount tier for an order amount of $4,200 from a table sorted ascending by minimum spend. Which match_mode value is correct? - A) 0 (exact match only) - B) -1 (exact, or next smaller) - C) 1 (exact, or next larger) - D) 2 (wildcard)

Q7. XLOOKUP — search_mode for Most Recent

You want XLOOKUP to return the most recent transaction amount for CUST-001 from a chronologically ordered log. Which search_mode value is correct? - A) 1 (first-to-last, default) - B) -1 (last-to-first) - C) 2 (binary search ascending) - D) -2 (binary search descending)

Q8. XLOOKUP — Wildcards

Your XLOOKUP formula uses "Acme*" as the lookup value, but it returns #N/A even though "Acme Corp" exists in the lookup column. What did you forget? - A) The lookup column must be sorted alphabetically. - B) The 5th argument (match_mode) must be set to 2 to enable wildcards. - C) XLOOKUP does not support wildcards — use VLOOKUP. - D) Wildcards must be wrapped in square brackets: [Acme*].

Q9. INDEX/MATCH — 2-D Lookup

You have a matrix with months down column A and product names across row 1, with sales values in the interior. Which formula returns the sales for "Widget" in "April" (H2 = "April", H3 = "Widget")? - A) =VLOOKUP(H2, A1:G13, MATCH(H3, A1:G1, 0), FALSE) - B) =INDEX(B2:G13, MATCH(H2, A2:A13, 0), MATCH(H3, B1:G1, 0)) - C) =HLOOKUP(H3, A1:G13, MATCH(H2, A:A, 0)) - D) =LOOKUP(H2&H3, A2:G13)

Q10. XMATCH — Find the Last Match

You want the position of the last "Pending" status in Orders[Status] (a Microsoft 365 workbook). Which formula does this? - A) =MATCH("Pending", Orders[Status], 0) - B) =XMATCH("Pending", Orders[Status], 0, -1) - C) =XMATCH("Pending", Orders[Status], -1, 1) - D) =COUNTIF(Orders[Status], "Pending")

Section C: Lesson 3.3 — Date & Time Functions

Q11. NETWORKDAYS

You need to count the business days between a project start (A2) and project end (B2), excluding company holidays listed in Holidays!A:A. Which formula is correct? - A) =B2-A2 - B) =NETWORKDAYS(A2, B2, Holidays!A:A) - C) =WORKDAY(A2, B2, Holidays!A:A) - D) =DAYS(B2, A2) - 2

Q12. NETWORKDAYS.INTL — Custom Weekend

A team in Israel works Sun-Thu (weekend = Fri & Sat). Which weekend argument tells NETWORKDAYS.INTL to treat Fri/Sat as the weekend? - A) 1 - B) 2 - C) 7 - D) 11

Q13. WORKDAY.INTL Bitmask

You need WORKDAY.INTL to use a custom weekend where only Friday is a non-working day (no other days off). Which bitmask string is correct (read Mon, Tue, Wed, Thu, Fri, Sat, Sun)? - A) "0000011" - B) "0000100" - C) "1000000" - D) "0000001"

Q14. DATEDIF — Tenure

An employee was hired on 2018-04-15. Today is 2026-05-03. Which formula returns the complete years of tenure, ignoring partial-year fractions? - A) =YEARFRAC(HireDate, TODAY()) - B) =DATEDIF(HireDate, TODAY(), "Y") - C) =YEAR(TODAY()) - YEAR(HireDate) - D) =(TODAY() - HireDate) / 365.25

Q15. EOMONTH — Start of Next Month

Cell A2 contains 2026-05-15. Which formula returns 2026-06-01 (the first day of the next month)? - A) =EOMONTH(A2, 1) - B) =EOMONTH(A2, 0) + 1 - C) =EOMONTH(A2, -1) + 1 - D) =DATE(YEAR(A2), MONTH(A2)+1, 31)

Q16. EDATE — Anniversary

A subscription started on 2024-01-31. Which formula returns the renewal date one month later? - A) =A2 + 30 - B) =A2 + 31 - C) =EDATE(A2, 1) — returns 2024-02-29 (Excel clamps to the last valid day). - D) =DATE(YEAR(A2), MONTH(A2)+1, DAY(A2)) — returns #NUM! because Feb has no day 31.

Section D: Lesson 3.4 — What-If Analysis

Q17. Goal Seek vs Scenario Manager

A finance lead wants to compare three pricing scenarios — Conservative, Base, and Aggressive — that each change the unit price, the marketing budget, and the discount rate. Which What-If tool produces a side-by-side comparison report of the resulting profit under each scenario? - A) Goal Seek (single variable, single target). - B) Scenario Manager with a Scenario Summary report. - C) A one-variable Data Table. - D) The FORECAST.LINEAR function.

Q18. Goal Seek Pre-flight

Goal Seek refuses to run when you set the Set cell to B5. What is the most likely problem? - A) B5 is on a hidden sheet. - B) B5 contains a hard-coded number rather than a formula. - C) The workbook is in Manual calculation mode. - D) Goal Seek requires the By changing cell to be on a different sheet.

Q19. Scenario Manager — Max Cells

Scenario Manager allows up to how many changing cells per scenario? - A) 8 - B) 16 - C) 32 - D) 256

Q20. Two-Variable Data Table

When building a two-variable Data Table, where must the formula be placed? - A) In the cell directly above the column of input values. - B) In the cell directly to the left of the row of input values. - C) In the corner cell at the top-left of the grid (intersection of the row and column inputs). - D) Anywhere on the worksheet — Excel finds it automatically.

Q21. One-Variable Data Table — Multiple Outputs

You build a one-variable Data Table to test interest rates against a PMT formula. You want to also see total interest paid for each rate, side by side. Where does the second formula go? - A) The Data Table feature only supports one output per table. - B) In the cell adjacent to the first formula, in the same row (column-oriented input list). - C) In a separate Data Table — they cannot share the input column. - D) In the corner cell, joined to the first formula by &.

Q22. Recalculation Mode

Your model has 200,000 formulas plus a 50×50 Data Table; every cell edit causes a long pause. Which calculation mode helps? - A) Automatic. - B) Automatic Except for Data Tables. - C) Manual. - D) Iterative.

Section E: Lesson 3.5 — Dynamic Arrays

Q23. SPILL Error

A formula that used to return a single value now shows #SPILL!. What is happening? - A) The formula has a divide-by-zero error. - B) The formula returns multiple values, but the spill range contains a non-empty cell or a merged cell. - C) The workbook is in manual calculation mode. - D) The formula references a deleted cell.

Q24. SORT vs SORTBY

You want to display a list of names sorted by a separate column of scores (descending), without showing the scores themselves. Which function is correct? - A) =SORT(Names, Scores, -1) — SORT accepts a separate sort key. - B) =SORTBY(Names, Scores, -1) — SORTBY sorts an array by a different array. - C) =SORT(Scores, Names, -1) — pass the names as the sort order. - D) =LARGE(Names, Scores).

Q25. Spilled Range Operator

A UNIQUE formula in E2 spills four values down to E5. You want a COUNTA of the entire spill range that automatically grows when more values appear. Which reference is correct? - A) =COUNTA(E2:E5) - B) =COUNTA(E:E) - C) =COUNTA(E2#) - D) =COUNTA(SPILL(E2))

Q26. TEXTSPLIT

Cell A2 contains the literal text "Smith, John, Engineer". Which formula spills the three parts into adjacent cells across a row? - A) =TEXTSPLIT(A2, ",") - B) =TEXTSPLIT(A2, ", ") - C) =SPLIT(A2, ", ") - D) =TEXT.SPLIT(A2, ",", " ")

Q27. TEXTBEFORE — Nth Delimiter

Cell A2 contains "PROJ-2024-NORTH-001". Which formula returns "PROJ-2024" (everything before the second hyphen)? - A) =TEXTBEFORE(A2, "-") - B) =TEXTBEFORE(A2, "-", 2) - C) =LEFT(A2, FIND("-", A2, 1)) - D) =TEXTAFTER(A2, "-", -2)

Section F: Lesson 3.6 — Financial Functions

Q28. PMT Sign

You use =PMT(0.06/12, 360, 200000) for a $200,000 mortgage at 6% APR over 30 years and you get a negative number. Why? - A) The interest rate is too high. - B) PMT returns money you pay out as a negative number; pass the present value as -200000 (or negate the result) to display a positive payment. - C) The function is broken; use IPMT instead. - D) The number of periods should be 30, not 360.

Q29. IPMT vs PPMT

On the first month of a 60-month $530,000 loan at 6% APR, which statement is true? - A) IPMT(6%/12, 1, 60, 530000) returns the principal portion of the payment. - B) IPMT returns the interest portion; PPMT returns the principal portion. Together they equal PMT. - C) IPMT and PPMT return the same value — you only need one. - D) Neither function exists in modern Excel; use PMT divided by 2.

Q30. XIRR Inputs

Which arguments does XIRR require? - A) A single rate and a single number of periods. - B) An array of cash-flow values and an array of corresponding dates. - C) Loan amount, interest rate, and term — the same as PMT. - D) A guess and an array of monthly payments.

Section G: Lesson 3.7 — Troubleshooting Formulas

Q31. Show Formulas Shortcut

Which keyboard shortcut toggles every cell on the active sheet between displaying values and displaying the underlying formula text? - A) Ctrl + Shift + F - B) F9 - C) Ctrl + ` (grave accent) - D) Alt + =

Q32. IFERROR Hides Real Bugs

You wrap a VLOOKUP in IFERROR(..., "Not found"). Months later, someone deletes the lookup column entirely. What does the user now see? - A) #REF! - B) "Not found" — masking a real bug. - C) #N/A - D) Excel auto-repairs the formula.

Q33. IFNA vs IFERROR

When wrapping a lookup, why might you prefer IFNA over IFERROR? - A) IFNA is faster than IFERROR. - B) IFNA only catches #N/A, allowing other errors (#REF!, #VALUE!) to surface so genuine bugs are visible. - C) IFNA works on older versions of Excel; IFERROR does not. - D) IFNA accepts more arguments than IFERROR.

Q34. Error Type — Deleted Column

A cell shows #REF!. Which root cause matches? - A) A lookup function found no match. - B) A column or cell the formula referenced has been deleted. - C) A function name was misspelled. - D) Two ranges were combined with a space and do not actually intersect.

Q35. Trace Precedents

Which keyboard shortcut selects the precedent cells (jumps to the source cells) of the active formula? - A) Ctrl + [ - B) Ctrl + ] - C) Ctrl + Shift + [ - D) F2

Section H: Lesson 3.8 — Macros

Q36. Personal Macro Workbook

You record a macro and want it to be available in every workbook you open from now on. In the Record Macro dialog, which Store macro in option must you choose? - A) This Workbook. - B) New Workbook. - C) Personal Macro Workbook. - D) Add-in (.xlam).

Q37. PERSONAL.XLSB Location

The Personal Macro Workbook lives at which default path? - A) C:\Program Files\Microsoft Office\Excel\PERSONAL.XLSB - B) %APPDATA%\Microsoft\Excel\XLSTART\PERSONAL.XLSB - C) %TEMP%\Excel\PERSONAL.XLSB - D) C:\Users\Public\Documents\PERSONAL.XLSB

Q38. Editing PERSONAL.XLSB

Why must you click View > Unhide before editing macros stored in the Personal Macro Workbook? - A) Excel encrypts hidden workbooks; unhiding decrypts them. - B) PERSONAL.XLSB opens hidden by default — you must unhide it before its modules can be edited in the VBE Project Explorer. - C) Unhiding is required to comply with macro security policy. - D) The VBE refuses to open .xlsb files unless they are visible.

Q39. Form Controls vs ActiveX

Which statement about Form Controls is correct? - A) Form Controls have full event handlers like _Click and _Change you can write VBA against. - B) Form Controls are simpler than ActiveX, do not expose properties/events, and continue to work in protected workbooks and on Excel for Mac. - C) Form Controls are Windows-only; ActiveX works cross-platform. - D) Form Controls require an internet connection to render.

Q40. Assigning a Macro to a Button

You drew a Form Control button on the worksheet but the Assign Macro dialog never appeared. What did you do wrong? - A) You picked ActiveX > Command Button from the second row of the dropdown rather than Form Controls > Button from the first row. ActiveX buttons require a separate VBA event handler. - B) The workbook is not macro-enabled. - C) You drew the button outside the visible window area. - D) The Developer tab needs to be re-enabled.

Q41. Check Box Cell Link

A Form Control Check Box is linked to cell D1. What value does D1 show when the box is ticked? - A) 1 - B) Yes - C) TRUE - D) The text label of the check box.

Section I: Lesson 3.9 — Consolidate

Q42. Live Consolidate

You need to combine four regional sales sheets (North, South, East, West) into one summary table that updates automatically when the source data changes. In the Data > Consolidate dialog, which option enables live updating? - A) Use labels in Top row. - B) Use labels in Left column. - C) Create links to source data. - D) Browse….

Q43. Consolidate by Position

Your four source sheets all have identical column orders and identical row labels. Which Consolidate setting is fastest? - A) Tick both Top row and Left column. - B) Tick neither — match by position. - C) Tick Top row only. - D) Use Power Query Append instead.

Section J: Lesson 3.10 — Forecast Sheet

Q44. Forecast Sheet Pre-flight

You have 14 months of monthly sales data and you want to forecast the next 6 months using Data > Forecast Sheet. What is the most likely problem? - A) Excel will refuse to open the dialog. - B) Forecast Sheet needs at least two full seasonal cycles — 24 months for a yearly cycle — so seasonality detection will likely fall back to a non-seasonal model. - C) The chart will only support a Column type. - D) The Confidence Interval cannot be set.

Q45. FORECAST family

Which function is the modern replacement for the legacy FORECAST function and is appropriate when your data has a clear linear trend but no seasonality? - A) FORECAST.ETS - B) FORECAST.ETS.SEASONALITY - C) FORECAST.ETS.CONFINT - D) FORECAST.LINEAR

Q46. Confidence Interval

You want a tighter, less conservative forecast band. Which Forecast Sheet option do you adjust? - A) Set Seasonality to 1. - B) Lower the Confidence Interval percentage from 95% to (say) 80%. - C) Untick Fill Missing Points Using Interpolation. - D) Switch the chart type from Line to Column.

Q47. Manual Seasonality Override

You know your data has a strict monthly cycle but Forecast Sheet's auto-detection picked a 7-day pattern. How do you override it? - A) Forecast Sheet's seasonality is fixed by the algorithm; there is no override. - B) Expand Options in the Create Forecast Worksheet dialog and switch Seasonality from Detect Automatically to Set Manually = 12. - C) Convert the dates to ISO strings before opening the dialog. - D) Replace FORECAST.ETS with FORECAST.LINEAR.

Part 2: True or False

Q48. A one-variable Data Table can show multiple formula outputs (e.g., monthly payment and total interest) for each input value, but a two-variable Data Table can only show one output. (True / False)

Q49. IFERROR and IFNA are interchangeable — both catch the same set of errors. (True / False)

Q50. The calculation mode Automatic Except for Data Tables exists because Data Tables can be very expensive to recompute and would otherwise slow down typing in the workbook. (True / False)

Q51. To edit macros stored in your Personal Macro Workbook you must first Unhide PERSONAL.XLSB from the View tab. (True / False)

Q52. Saving a workbook that contains macros as .xlsx will silently strip the VBA code. (True / False)

Q53. XLOOKUP's default match_mode is exact match, unlike VLOOKUP's default which is approximate match. (True / False)

Q54. DATEDIF appears in IntelliSense and is fully documented inside the Excel app, just like every other date function. (True / False)

Q55. A trendline on a chart and a Forecast Sheet both produce confidence-bounded forecasts using the AAA-ETS algorithm. (True / False)

Q56. Form Controls can be programmed against with _Click event handlers, the same way ActiveX controls are. (True / False)

Q57. Pressing F9 while a portion of a formula is highlighted in the formula bar evaluates that portion in place — useful for debugging. (True / False)

Part 3: Hands-On Micro-Tasks

Open Excel and perform each task. The exam will grade by the resulting workbook state, not by the path you took. Reference workbook: practice/module-3-finance-and-formulas.xlsx.

Task A — Forecast Sheet

Open the MonthlySales sheet (Date | Sales spanning at least 24 months).
Select both columns including headers.
On the Data tab, click Forecast Sheet.
Set Forecast End to 12 months past the last data point.
Expand Options and set Confidence Interval to 90% and Seasonality to Set Manually = 12.
Click Create.

Success criteria: a new worksheet appears containing historical and forecast values, an upper/lower bound at 90%, and a line chart.

Task B — Two-Variable Data Table

On the LoanModel sheet, place =PMT(C1/12, C2*12, -250000) in B2.
Put 0.03, 0.04, 0.05, 0.06, 0.07 in B3:B7 (rates).
Put 15, 20, 25, 30 in C2:F2 (loan terms in years).
Select B2:F7.
Data > What-If Analysis > Data Table… → Row input cell: C2, Column input cell: C1.
Click OK.

Success criteria: the interior of the grid (C3:F7) is filled with monthly payment values that vary down (rate) and across (term).

Task C — XLOOKUP with match_mode

On the Tiers sheet, the Min_Spend column lists 0, 1000, 5000, 10000 ascending; the Discount_Pct column lists 0%, 5%, 10%, 15%. In cell H2 write a formula that returns the discount tier for the order amount in G2 (try 4200). Use XLOOKUP with the appropriate match_mode.

Success criteria: H2 returns 5% for an order of 4200.

Task D — DATEDIF Tenure Breakdown

On the Employees sheet, in cell F2 write a single formula that produces a string like "7 years, 4 months" showing tenure between HireDate (column D) and TODAY(). Fill the formula down to the rest of the table.

Success criteria: F2 reads "7 years, 4 months" (or whatever is correct as of the test date).

Task E — Trace Error

On the Audit sheet, in A1 enter 0. In B1 enter =10/A1. In C1 enter =B1*5. In D1 enter =C1+100.
Select D1 (which now shows #DIV/0!).
Formulas > Error Checking dropdown > Trace Error.
Observe the red and blue arrows.
Use Remove Arrows to clean up.

Success criteria: arrows correctly identify A1 as the source of the divide-by-zero error.

Task F — Personal Macro Workbook

Developer > Record Macro. Name it BoldHeader. Store macro in: Personal Macro Workbook.
Select A1, press Ctrl + B, click Stop Recording.
Close Excel. When prompted to save PERSONAL.XLSB, click Save.
Reopen Excel with any new blank workbook and run the macro from View > Macros.

Success criteria: BoldHeader appears in the macro list of the brand-new workbook and runs successfully.

Task G — Form Control Button

On the Dashboard sheet, Developer > Insert > Button (Form Control).
Draw a button approximately covering H2:I3.
In the Assign Macro dialog, pick the existing RefreshAll macro and click OK.
Right-click the button > Edit Text → type Refresh.

Success criteria: clicking the button runs the assigned macro.

Task H — Consolidate with Links

Build three small regional sheets Q1, Q2, Q3, each with the same row labels in column A and a numeric column B.
On a new sheet, click the top-left destination cell.
Data > Consolidate, Function: Sum, add each Q1!$A$1:$B$10, Q2!…, Q3!… to the All references list.
Tick Top row, Left column, and Create links to source data.
Click OK.

Success criteria: the consolidated sheet contains an outlined, expandable summary that re-totals when source values change.

Task I — Goal Seek

On the WhatIf sheet, in B1 enter 200 (units sold). In B2 enter 50 (price). In B3 enter =B1*B2.
Use Data > What-If Analysis > Goal Seek to find the unit count needed to hit 15000 revenue, by changing B1.

Success criteria: B1 updates to 300, and B3 reads 15000.

Task J — TEXTSPLIT and TEXTAFTER

On the Contacts sheet, column A contains email addresses such as alice@contoso.com. In column B, write a formula using TEXTAFTER to extract the domain (contoso.com). Then in column C use TEXTSPLIT to split the value in column A by @ so that the local part lands in C and the domain lands in D.

Success criteria: B2 reads "contoso.com" for alice@contoso.com; C2:D2 spill the parts into two cells.

Show answers

### Section A — Lesson 3.1 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q1** | **C** | `IFS` evaluates conditions top-to-bottom and returns the first match — much flatter than nested `IF`. `SWITCH` is for exact value matches, not ranges. | | **Q2** | **B** | `SWITCH` is purpose-built for one expression matched against an exact value list. `IFS` works but is more verbose. | | **Q3** | **B** | `SUMIFS` takes the sum range first, then alternating criteria-range/criteria pairs. Multiple criteria are combined with AND by default. | | **Q4** | **B** | `LET` defines names inside the formula, computes the expression once, and reuses the name — improving both performance and readability. | ### Section B — Lesson 3.2 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q5** | **B** | Trailing spaces are the #1 cause of "looks identical but doesn't match." Use `TRIM` or `CLEAN` to normalize, or compare with `EXACT`. | | **Q6** | **B** | `match_mode = -1` means exact, or **next smaller**. Perfect for tier lookups where the tier table lists each minimum threshold. | | **Q7** | **B** | `search_mode = -1` scans the array bottom-to-top, returning the last match. The lookup array does not need to be sorted in reverse. | | **Q8** | **B** | Unlike `VLOOKUP`, `XLOOKUP` does not treat `*` and `?` as wildcards by default. You must opt in with `match_mode = 2`. | | **Q9** | **B** | The 2-D `INDEX(MATCH, MATCH)` pattern: row position from the first `MATCH`, column position from the second, fed into `INDEX` over the value grid. | | **Q10** | **B** | `XMATCH` adds the `search_mode` argument; `-1` scans bottom-to-top, returning the position of the **last** matching value. | ### Section C — Lesson 3.3 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q11** | **B** | `NETWORKDAYS(start, end, [holidays])` returns whole working days, automatically excluding Saturdays, Sundays, and any dates in the holidays list. | | **Q12** | **C** | Code `7` in `NETWORKDAYS.INTL` = Friday & Saturday weekend (the Israeli convention). Code `1` is the default Sat/Sun. | | **Q13** | **B** | The bitmask reads Mon-Sun, with `1` = weekend. `"0000100"` makes only Friday a weekend day. | | **Q14** | **B** | `DATEDIF(start, end, "Y")` returns the number of complete years between two dates. `YEARFRAC` returns a decimal; `YEAR()-YEAR()` ignores month/day. | | **Q15** | **B** | `EOMONTH(A2, 0)` returns the last day of the current month; adding `1` gives the first of next month. `EOMONTH(A2, 1)` returns the last day of next month. | | **Q16** | **C** | `EDATE` clamps to the last valid day when the day-of-month doesn't exist in the target month — `EDATE("2024-01-31", 1)` returns `2024-02-29` in a leap year. | ### Section D — Lesson 3.4 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q17** | **B** | Scenario Manager is purpose-built for comparing named scenarios that each set multiple input cells. Its **Summary** button produces a side-by-side profit report. Goal Seek changes a single input to hit a target; a Data Table varies one or two inputs against one formula; `FORECAST.LINEAR` is for time-series prediction. | | **Q18** | **B** | Goal Seek requires the **Set cell** to contain a formula. A hard-coded number cannot be solved against. | | **Q19** | **C** | Up to **32 changing cells** per scenario. | | **Q20** | **C** | The corner cell holds the formula; the row of values runs across the top, the column of values runs down the left. | | **Q21** | **B** | A one-variable Data Table allows multiple formulas adjacent to the first (one column per output). The two-variable variant only supports one output (the corner formula). | | **Q22** | **B** | **Automatic Except for Data Tables** keeps every other formula live but defers Data Table recalc until you press `F9`. | ### Section E — Lesson 3.5 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q23** | **B** | `#SPILL!` means the dynamic array tried to spill into a range that already had content (or a merged cell, or off the edge of the sheet). | | **Q24** | **B** | `SORT` sorts an array by **its own** values; `SORTBY` sorts an array by a **separate** key array. | | **Q25** | **C** | The `#` operator references the **entire spill range** of a dynamic-array formula by pointing at its top-left cell. | | **Q26** | **B** | The delimiter must match the actual literal in the source — `", "` (comma followed by space) cleanly produces `Smith`, `John`, `Engineer`. | | **Q27** | **B** | `TEXTBEFORE(text, delim, instance_num)` slices before the *Nth* occurrence of the delimiter. `2` returns everything before the second hyphen. | ### Section F — Lesson 3.6 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q28** | **B** | `PMT` follows the cash-flow sign convention: outflows are negative. Pass `-200000` for the present value or wrap the result in `-PMT(…)` to display a positive payment. | | **Q29** | **B** | `IPMT` returns the interest portion of a single payment; `PPMT` returns the principal portion. Their sum equals `PMT`. | | **Q30** | **B** | `XIRR(values, dates)` accepts irregular cash flows by pairing each value with its actual date — unlike `IRR`, which assumes evenly spaced periods. | ### Section G — Lesson 3.7 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q31** | **C** | `` Ctrl + ` `` (grave accent) toggles **Show Formulas**. | | **Q32** | **B** | `IFERROR` masks every error type, including the `#REF!` from a deleted column. Use `IFNA` for lookups so genuine bugs surface. | | **Q33** | **B** | `IFNA` only catches `#N/A`. Other errors like `#REF!` or `#VALUE!` continue to display, exposing real bugs instead of hiding them under "Not found." | | **Q34** | **B** | `#REF!` specifically means a reference points "nowhere" because the referent was deleted. | | **Q35** | **A** | `Ctrl + [` selects precedents (jumps to the source); `Ctrl + ]` selects dependents (jumps to formulas using this cell). | ### Section H — Lesson 3.8 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q36** | **C** | The Personal Macro Workbook (`PERSONAL.XLSB`) is auto-loaded hidden every Excel session, making its macros available everywhere. | | **Q37** | **B** | The default path is `%APPDATA%\Microsoft\Excel\XLSTART\PERSONAL.XLSB`. Files inside `XLSTART` open automatically when Excel launches. | | **Q38** | **B** | `PERSONAL.XLSB` opens hidden by default. **View > Unhide > PERSONAL** brings it into view so you can edit its modules in the VBE. | | **Q39** | **B** | Form Controls are simpler, cross-platform, and survive workbook protection; ActiveX is more powerful but Windows-only and often blocked by IT policy. | | **Q40** | **A** | The Form Controls row (top of the Insert dropdown) opens the Assign Macro dialog automatically. ActiveX buttons require you to write your own `_Click` event in the VBE. | | **Q41** | **C** | A Form Control Check Box's **Cell link** receives `TRUE` when ticked, `FALSE` when unticked. Use it inside an `IF` to drive other formulas. | ### Section I — Lesson 3.9 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q42** | **C** | **Create links to source data** writes formulas (with outlining) so the consolidated sheet re-totals when the sources change. | | **Q43** | **B** | When all source layouts are identical, **position-based** consolidation (neither **Top row** nor **Left column** ticked) is the fastest and avoids label-matching surprises. | ### Section J — Lesson 3.10 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q44** | **B** | Forecast Sheet's seasonality detection needs at least two complete cycles — 24 months for a yearly seasonal pattern. With only 14 months it will fall back to a non-seasonal model. | | **Q45** | **D** | `FORECAST.LINEAR` is the modern, named replacement for the legacy `FORECAST` function. | | **Q46** | **B** | The Confidence Interval percentage controls how wide the prediction band is. 80% is tighter than 95%; 99% is wider. | | **Q47** | **B** | Expand **Options** in the dialog and switch **Seasonality** to **Set Manually**, entering `12` for monthly data. `1` (or unchecked) forces no seasonality. | ### Part 2 — True/False Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q48** | **True** | A one-variable Data Table can place multiple formulas in adjacent columns (or rows). A two-variable table is constrained to one corner formula and therefore one output. | | **Q49** | **False** | `IFERROR` catches every error type; `IFNA` catches only `#N/A`. Prefer `IFNA` for lookups so other errors remain visible. | | **Q50** | **True** | A large grid is a separate calculation per cell; recomputing on every keystroke would be unworkable, hence the dedicated mode. | | **Q51** | **True** | `PERSONAL.XLSB` opens hidden by default. Use **View > Unhide** to expose it before editing in the VBE. | | **Q52** | **True** | `.xlsx` is the macro-free format. Saving a macro-containing workbook as `.xlsx` strips the VBA project without warning the user a second time. | | **Q53** | **True** | `XLOOKUP`'s default `match_mode` is `0` (exact match). `VLOOKUP` defaults to approximate match (TRUE) — a frequent source of silent errors. | | **Q54** | **False** | `DATEDIF` is the famous "hidden" function — it works but does **not** appear in IntelliSense and is not documented inside the Excel app. | | **Q55** | **False** | A chart trendline is a single visual regression line with no confidence band. Forecast Sheet uses ETS with confidence bounds. | | **Q56** | **False** | Form Controls expose only **Format Control** properties and a single **Assign Macro** hook — no `_Click` events. ActiveX has full event support. | | **Q57** | **True** | Highlighting part of a formula in the formula bar and pressing `F9` evaluates that fragment in place. Press `Esc` to abandon the change without committing. | ### Part 3 — Hands-On Solutions **Task A.** Make sure dates are evenly spaced and at least 24 months long, otherwise seasonality `= 12` will produce a poor fit. The new sheet must contain historical, forecast, lower-bound, upper-bound columns plus a chart. **Task B.** The formula must live in the **corner** cell (`B2`). Putting it elsewhere is the most common reason a two-variable Data Table fills with zeros. **Row input** = the cell that the *row* of values substitutes into; **Column input** = the cell that the *column* of values substitutes into. **Task C.** Write `=XLOOKUP(G2, Tiers[Min_Spend], Tiers[Discount_Pct], 0, -1)`. The 5th argument `-1` selects exact-or-next-smaller. For `4200`, the lookup falls to the `1000` tier → `5%`. **Task D.** Use:

=DATEDIF(D2, TODAY(), "Y") & " years, " & DATEDIF(D2, TODAY(), "YM") & " months"

`"Y"` returns whole years; `"YM"` returns the residual months after subtracting whole years. **Task E.** If **Trace Error** is greyed out, the selected cell does not currently contain an error. The arrows show `D1 → C1 → B1 → A1` (red along the broken chain) confirming `A1 = 0` is the divide-by-zero source. **Task F.** If you forget to **Save** `PERSONAL.XLSB` on close, the macro is lost. Verify by closing Excel completely, reopening with `Ctrl + N`, and confirming `PERSONAL.XLSB!BoldHeader` is in the Macros list. **Task G.** Make sure you pick **Form Controls > Button** (top row of the Insert dropdown), not **ActiveX > Command Button**. Right-click the button to change the assigned macro afterwards via **Assign Macro**. **Task H.** Without **Create links to source data**, the consolidated table is a one-shot snapshot and will not update. With links enabled, Excel writes external-reference formulas and groups the detail rows under outline level 2. **Task I.** Goal Seek requires the **Set cell** to contain a formula (not a hard-coded number) and the **By changing cell** to be a precedent of that formula. After the run, `B1` = `300` and `B3` = `15000`. **Task J.** `=TEXTAFTER(A2, "@")` returns the domain. `=TEXTSPLIT(A2, "@")` spills `local | domain` across two cells.

Exam Tip — Module 3 Coverage Map:

Lesson	Most-Tested Concept
3.1	`IFS` / `SWITCH` flatten nested `IF`s; `SUMIFS` puts the sum range first; `LET` names sub-expressions for reuse.
3.2	`XLOOKUP` defaults to exact match; `match_mode = -1` for tiers, `2` for wildcards; `search_mode = -1` for "most recent"; 2-D `INDEX(MATCH, MATCH)` for grids.
3.3	`NETWORKDAYS.INTL` weekend codes; `DATEDIF` units `"Y"`/`"M"`/`"YM"`; `EOMONTH(A2,-1)+1` for start-of-month; `EDATE` clamps month-end.
3.4	Goal Seek = single variable; Solver = constraints; corner-cell formula for 2-variable Data Table; Automatic Except for Data Tables mode.
3.5	`#SPILL!` causes; `SORT` vs `SORTBY`; `E2#` spilled-range operator; `TEXTSPLIT` / `TEXTBEFORE` / `TEXTAFTER`.
3.6	Sign convention — outflows negative; `IPMT` + `PPMT` = `PMT`; `XIRR(values, dates)` for irregular cash flows.
3.7	Ctrl + ` = Show Formulas; `IFNA` over `IFERROR` for lookups; `Ctrl + [` = trace precedents; `#REF!` = deleted reference.
3.8	Personal Macro Workbook in `XLSTART`; View > Unhide to edit; Form Controls vs ActiveX; Form Control Check Box returns `TRUE`/`FALSE`.
3.9	Create links to source data for live consolidation; position-based when layouts match.
3.10	Two full seasonal cycles minimum; Set Manually = 12 for monthly seasonality; lower Confidence Interval = tighter band; `FORECAST.LINEAR` for trend without seasonality.