On the Shopping List sheet, check all the formulas.

On the Shopping List sheet, check all the formulas. Cells to check are filled with the light purple color. a. b. The formula in cell B2 uses the wrong function. The formula in cell H9 results in the correct value. However, the workbook author copied this formula to the remaining cells in the column and those values are definitely not correct! Fix the formula in cell H9 and copy it to cells H10:H23. Hint. Notice that cell H8 needs to be referenced in every formula because it contains the tax percentage. 5. If you’ve fixed the formulas in cells H9:H23 correctly, the formulas in cells I9:I23 and G5 should calculate properly now. However, the formulas in cells G2:G4 still have errors that need to be fixed. It might be helpful to display the formulas on-screen for easy viewing. a. Correct the function used in cell G2 to display the average value of the Cost column. b. Correct the function used in cell G3 to display the largest value in the Cost column. c. Correct the function used in cell G4 to display the smallest value in the Cost column. 6. On the Summary sheet, you will be fixing and entering formulas. Cells to complete are filled with the light purple color. It might be helpful to display the formulas on-screen for easy viewing. a. Cell B2 should use a function that will update the date to the current date every time the workbook is opened. b. Cell B4 should reference cell A4 on the Guest List sheet. c. Cell B5 should reference cell A3 on the Guest List sheet. d. Cell B8 contains a formula that should add the value in F6 from the Guest List sheet plus the value in G5 from the Shopping List sheet. There is an error in this formula that you need to fix. e. Add a formula to cell B9 to calculate the amount to borrow. This needs to be the Total Cost of the Event (B8) minus the Cash Available for Event (B7).

The Correct Answer and Explanation is:

Corrected formulas

Sheet	Cell	Correct formula	What it does
Shopping List	B2	`=SUM(B9:B23)`	Adds every value in the Quantity column so you see the total number of units you have to buy instead of merely counting how many rows happen to contain numbers.
	H9	`=F9*$H$8`	Multiplies the cost of the first item (F9) by the sales-tax rate that lives in H8. Copy (fill-handle) this down through H23. Because H8 is locked with `$` signs, every copied formula still looks at the single tax rate instead of drifting to H9, H10…
Shopping List	H10 : H23	Copied from the fixed H9	Each row now shows “tax on this item”.
	G2	`=AVERAGE(F9:F23)`	Returns the mean value of the Cost column.
	G3	`=MAX(F9:F23)`	Shows the single most-expensive line item.
	G4	`=MIN(F9:F23)`	Shows the cheapest line item.
Summary	B2	`=TODAY()`	Inserts today’s date and updates automatically every time the file opens.
	B4	`='Guest List'!A4`	Pulls the value sitting in A4 on the Guest List sheet.
	B5	`='Guest List'!A3`	Pulls A3 from Guest List.
	B8	`='Guest List'!F6 + 'Shopping List'!G5`	Adds total guests’ cost (F6 on the Guest List sheet) to total shopping cost (G5 on the Shopping List sheet) to give the Total Cost of the Event.
	B9	`=B8 - B7`	Calculates Amount to Borrow by subtracting cash-on-hand (B7) from the total cost (B8).

Why each change matters

Spreadsheets are basically gigantic calculators, and—just as with a handheld calculator—your result is only as good as the formula you key in. The purple-shaded cells in this workbook had two broad categories of problems:

Wrong function choices – the formula used the right range but asked Excel to perform the wrong mathematical operation (for example, counting instead of summing).
Faulty relative/absolute references – the formula was fine in its home row, but because the cell references were all relative (no $ symbols), copying it down made the reference march down as well, so every subsequent row pointed at the wrong input.

Let’s walk through the fixes one by one:

1. Shopping List ! B2 — choosing the correct aggregation function

The author’s original formula used COUNT (or COUNTA) to build a “Total Quantity” cell. COUNT returns the number of numeric cells in the range, whereas COUNTA returns the number of non-blank cells. Neither gives the sum of those numbers, which is what you need when you are asking, “How many units of stuff will I buy in total?” By replacing the function with SUM, we told Excel to add every quantity together:

=SUM(B9:B23)

This single change ripples forward: totals, averages, maximums, and minimums that rely on those quantities now have a correct numerator.

2. Shopping List ! H9 — locking the tax-rate cell

Sales tax is nearly always stored once in a header because there is only one rate for the whole sheet. When you multiply each line item’s cost by that rate you want that percentage to stay anchored while the line cost reference slides down through the rows.

The original author wrote (something like):

=F9*H8

That works in row 9, but copying to row 10 turns it into =F10*H9, and suddenly row 10 is using the tax‐dollar amount for row 9 as though it were the tax rate! By introducing absolute references for the tax-rate cell:

=F9*$H$8

we lock both the column (H) and row (8). Now, no matter where we copy it, that second factor is frozen on the tax percentage, giving a sound calculation for every item from H9 through H23.

A handy mnemonic:

“$ means stick.”

If the dollar sign is in front of the column letter, the column sticks. If it is in front of the row number, the row sticks.

3. Shopping List ! G2 : G4 — descriptive statistics on costs

After the tax error was removed, the grand-total cell G5 and the final-price column I populated correctly, but the quick statistics in G2-G4 were still broken. Each used an inappropriate function:

G2 was using SUM instead of AVERAGE (we want the mean cost, not the total).
G3 was using AVERAGE instead of MAX (we want the single greatest cost).
G4 was using COUNT or perhaps LARGE wrongly instead of MIN.

The fixes are straightforward but reveal an important modelling principle: always match the function to the business question. If the question contains words like “average”, “mean”, or “typical”, the AVERAGE function is your friend. Words like “highest”, “largest”, or “most expensive” signal MAX. Words like “lowest”, “smallest”, or “cheapest” point to MIN.

=AVERAGE(F9:F23)   'G2
=MAX(F9:F23)       'G3
=MIN(F9:F23)       'G4

Because the range F9:F23 is a block of numbers, these functions are robust and require no extra arguments.

4. Summary ! B2 — a perpetually up-to-date date

The Summary sheet is designed to tell the user, at a glance, what the file thinks “today” is. Using TODAY() is 100 % hands-off: every time the workbook opens or recalculates, Excel re-evaluates the function and plugs in the current system date. There’s no need for manual edits, no chance of forgetting to update.

=TODAY()

5. Summary ! B4 and B5 — pulling single values from another sheet

The workbook already has a Guest List sheet with vetted numbers. Instead of re-typing those numbers (and risking divergence or typo), you can reference them directly. Excel recognises anything in single quotes as a sheet name. The syntax is:

='Sheet Name'!CellAddress

Hence:

='Guest List'!A4   'B4
='Guest List'!A3   'B5

These are pure links—if someone updates the Guest List sheet tomorrow, the Summary sheet updates automatically.

6. Summary ! B8 — consolidating subtotals from two sheets

The total cost of your event is the cost of feeding/hosting the guests plus the cost of buying all the physical items. Those subtotals already live in two different cells on two different sheets:

F6 on Guest List
G5 on Shopping List

Therefore a simple addition is all you need:

='Guest List'!F6 + 'Shopping List'!G5

Note how clear it becomes when you put each operand on its own side of a plus sign; nested functions or over-clever constructions only invite errors.

7. Summary ! B9 — turning totals into a financing need

Once you know the total event cost, subtract the cash you already have set aside (cell B7). If the result is positive you need to borrow money; if it is negative or zero you’re already covered.

=B8 - B7

No sheet references are needed because both inputs are on the same sheet.

Broader lessons & best practices

One fact, one place. Store a datum (tax rate, price per guest, etc.) exactly once. Link every calculation back to that single cell. That way you can change the figure in one spot and watch the entire workbook refresh.
Use absolute references for constants, relative references for iterating. This is the essence of stretchy, copy-friendly formulas. Knowing when to anchor with $ saves hours of manual tweaking.
Choose verbs that match the math. The name of the function should echo the question you are asking. If the question’s verb is “add,” use SUM; if “average,” use AVERAGE, and so on. When a formula and its plain-English description diverge, one of them is wrong.
Avoid hard-coding numbers inside formulas. Enter 0.0825 (an 8.25 % tax rate) in a labelled cell and point to it; don’t scatter 0.0825 inside twenty different formulas. That eliminates hunt-and-replace nightmares.
Check formulas by switching to formula view (Ctrl + “`). Errors jump out when you can see every reference at once.
Test after every change. As you saw here, fixing H9 instantly healed I9:I23 and G5 without touching them. That feedback‐loop tells you the dependency chain is healthy.
Name your ranges for readability (e.g., TaxRate, CostColumn). Named ranges would let you write =Cost * TaxRate, which reads almost like English and is self-documenting.

By applying these seven corrections and the underlying principles, the workbook is now self-maintaining, transparent, and far less error-prone. Moreover, the Summary sheet has become a live dashboard: every time someone adjusts the guest count, the shopping quantities, or the tax rate, every downstream total adjusts instantly. That’s the power of well-structured formulas: they turn a static table into a resilient financial model that you can trust on event day—and long after.