{"id":224033,"date":"2025-06-02T16:11:44","date_gmt":"2025-06-02T16:11:44","guid":{"rendered":"https:\/\/learnexams.com\/blog\/?p=224033"},"modified":"2025-06-02T16:11:46","modified_gmt":"2025-06-02T16:11:46","slug":"on-the-shopping-list-sheet-check-all-the-formulas","status":"publish","type":"post","link":"https:\/\/www.learnexams.com\/blog\/2025\/06\/02\/on-the-shopping-list-sheet-check-all-the-formulas\/","title":{"rendered":"On the Shopping List sheet, check all the formulas."},"content":{"rendered":"\n

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).<\/p>\n\n\n\n

The Correct Answer and Explanation is:<\/mark><\/strong><\/p>\n\n\n\n

Corrected formulas<\/h2>\n\n\n\n
Sheet<\/th>Cell<\/th>Correct formula<\/th>What it does<\/th><\/tr><\/thead>
Shopping List<\/strong><\/td>B2<\/strong><\/td>=SUM(B9:B23)<\/code><\/td>Adds every value in the Quantity<\/strong> column so you see the total number of units you have to buy instead of merely counting how many rows happen to contain numbers.<\/td><\/tr>
<\/td>H9<\/strong><\/td>=F9*$H$8<\/code><\/td>Multiplies the cost of the first item (F9) by the sales-tax rate that lives in H8<\/strong>. Copy (fill-handle) this down through H23<\/strong>. Because H8<\/strong> is locked with $<\/code> signs, every copied formula still looks at the single tax rate instead of drifting to H9, H10\u2026<\/td><\/tr>
Shopping List<\/strong><\/td>H10 : H23<\/td>Copied from the fixed H9<\/em><\/td>Each row now shows \u201ctax on this item\u201d.<\/td><\/tr>
<\/td>G2<\/strong><\/td>=AVERAGE(F9:F23)<\/code><\/td>Returns the mean value of the Cost<\/strong> column.<\/td><\/tr>
<\/td>G3<\/strong><\/td>=MAX(F9:F23)<\/code><\/td>Shows the single most-expensive line item.<\/td><\/tr>
<\/td>G4<\/strong><\/td>=MIN(F9:F23)<\/code><\/td>Shows the cheapest line item.<\/td><\/tr>
Summary<\/strong><\/td>B2<\/strong><\/td>=TODAY()<\/code><\/td>Inserts today\u2019s date and updates automatically every time the file opens.<\/td><\/tr>
<\/td>B4<\/strong><\/td>='Guest List'!A4<\/code><\/td>Pulls the value sitting in A4 on the Guest List sheet.<\/td><\/tr>
<\/td>B5<\/strong><\/td>='Guest List'!A3<\/code><\/td>Pulls A3 from Guest List.<\/td><\/tr>
<\/td>B8<\/strong><\/td>='Guest List'!F6 + 'Shopping List'!G5<\/code><\/td>Adds total guests\u2019 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<\/strong>.<\/td><\/tr>
<\/td>B9<\/strong><\/td>=B8 - B7<\/code><\/td>Calculates Amount to Borrow<\/strong> by subtracting cash-on-hand (B7) from the total cost (B8).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

Why each change matters<\/h3>\n\n\n\n

Spreadsheets are basically gigantic calculators, and\u2014just as with a handheld calculator\u2014your result is only as good as the formula you key in. The purple-shaded cells in this workbook had two broad categories of problems:<\/p>\n\n\n\n

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

    Let\u2019s walk through the fixes one by one:<\/p>\n\n\n\n

    \n\n\n\n

    1. Shopping List ! B2 \u2014 choosing the correct aggregation function<\/h4>\n\n\n\n

    The author\u2019s original formula used COUNT<\/code> (or COUNTA<\/code>) to build a \u201cTotal Quantity\u201d cell. COUNT<\/code> returns the number of numeric cells in the range, whereas COUNTA<\/code> returns the number of non-blank cells. Neither gives the sum<\/strong> of those numbers, which is what you need when you are asking, \u201cHow many units of stuff will I buy in total?\u201d By replacing the function with SUM<\/code>, we told Excel to add every quantity together:<\/p>\n\n\n\n

    =SUM(B9:B23)\n<\/code><\/pre>\n\n\n\n
    This single change ripples forward: totals, averages, maximums, and minimums that rely on those quantities now have a correct numerator.<\/p>\n\n\n\n
    \n\n\n\n
    2. Shopping List ! H9 \u2014 locking the tax-rate cell<\/h4>\n\n\n\n
    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\u2019s cost by that rate you want that percentage to stay anchored while the line cost reference slides down through the rows.<\/p>\n\n\n\n
    The original author wrote (something like):<\/p>\n\n\n\n
    =F9*H8\n<\/code><\/pre>\n\n\n\n
    That works in row 9, but copying to row 10 turns it into =F10*H9<\/code>, and suddenly row 10 is using the tax\u2010dollar amount for row 9<\/strong> as though it were the tax rate! By introducing absolute references for the tax-rate cell:<\/p>\n\n\n\n
    =F9*$H$8\n<\/code><\/pre>\n\n\n\n
    we lock both the column<\/strong> (H) and row<\/strong> (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.<\/p>\n\n\n\n
    A handy mnemonic:<\/p>\n\n\n\n
    \n
    \u201c$<\/strong> means stick<\/strong>.\u201d<\/p>\n<\/blockquote>\n\n\n\n
    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.<\/p>\n\n\n\n
    \n\n\n\n
    3. Shopping List ! G2 : G4 \u2014 descriptive statistics on costs<\/h4>\n\n\n\n
    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:<\/p>\n\n\n\n
      \n
    • G2<\/strong> was using SUM<\/code> instead of AVERAGE<\/code> (we want the mean cost, not the total).<\/li>\n\n\n\n
    • G3<\/strong> was using AVERAGE<\/code> instead of MAX<\/code> (we want the single greatest cost).<\/li>\n\n\n\n
    • G4<\/strong> was using COUNT<\/code> or perhaps LARGE<\/code> wrongly instead of MIN<\/code>.<\/li>\n<\/ul>\n\n\n\n
      The fixes are straightforward but reveal an important modelling principle: always match the function to the business question.<\/strong> If the question contains words like \u201caverage\u201d, \u201cmean\u201d, or \u201ctypical\u201d, the AVERAGE<\/code> function is your friend. Words like \u201chighest\u201d, \u201clargest\u201d, or \u201cmost expensive\u201d signal MAX<\/code>. Words like \u201clowest\u201d, \u201csmallest\u201d, or \u201ccheapest\u201d point to MIN<\/code>.<\/p>\n\n\n\n
      =AVERAGE(F9:F23)   'G2\n=MAX(F9:F23)       'G3\n=MIN(F9:F23)       'G4\n<\/code><\/pre>\n\n\n\n
      Because the range F9:F23 is a block of numbers, these functions are robust and require no extra arguments.<\/p>\n\n\n\n
      \n\n\n\n
      4. Summary ! B2 \u2014 a perpetually up-to-date date<\/h4>\n\n\n\n
      The Summary sheet is designed to tell the user, at a glance, what the file thinks \u201ctoday\u201d<\/strong> is. Using TODAY()<\/code> is 100 % hands-off: every time the workbook opens or recalculates, Excel re-evaluates the function and plugs in the current system date. There\u2019s no need for manual edits, no chance of forgetting to update.<\/p>\n\n\n\n
      =TODAY()\n<\/code><\/pre>\n\n\n\n
      \n\n\n\n
      5. Summary ! B4 and B5 \u2014 pulling single values from another sheet<\/h4>\n\n\n\n
      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:<\/p>\n\n\n\n
      ='Sheet Name'!CellAddress\n<\/code><\/pre>\n\n\n\n
      Hence:<\/p>\n\n\n\n
      ='Guest List'!A4   'B4\n='Guest List'!A3   'B5\n<\/code><\/pre>\n\n\n\n
      These are pure links\u2014if someone updates the Guest List sheet tomorrow, the Summary sheet updates automatically.<\/p>\n\n\n\n
      \n\n\n\n
      6. Summary ! B8 \u2014 consolidating subtotals from two sheets<\/h4>\n\n\n\n
      The total cost of your event is the cost of feeding\/hosting the guests plus<\/strong> the cost of buying all the physical items. Those subtotals already live in two different cells on two different sheets:<\/p>\n\n\n\n
        \n
      • F6<\/code> on Guest List<\/strong><\/li>\n\n\n\n
      • G5<\/code> on Shopping List<\/strong><\/li>\n<\/ul>\n\n\n\n
        Therefore a simple addition is all you need:<\/p>\n\n\n\n
        ='Guest List'!F6 + 'Shopping List'!G5\n<\/code><\/pre>\n\n\n\n
        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.<\/p>\n\n\n\n
        \n\n\n\n
        7. Summary ! B9 \u2014 turning totals into a financing need<\/h4>\n\n\n\n
        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\u2019re already covered.<\/p>\n\n\n\n
        =B8 - B7\n<\/code><\/pre>\n\n\n\n
        No sheet references are needed because both inputs are on the same sheet.<\/p>\n\n\n\n
        \n\n\n\n
        Broader lessons & best practices<\/h3>\n\n\n\n
          \n
        1. One fact, one place.<\/strong> 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.<\/li>\n\n\n\n
        2. Use absolute references for constants, relative references for iterating.<\/strong> This is the essence of stretchy, copy-friendly formulas. Knowing when to anchor with $<\/code> saves hours of manual tweaking.<\/li>\n\n\n\n
        3. Choose verbs that match the math.<\/strong> The name of the function should echo the question you are asking. If the question\u2019s verb is \u201cadd,\u201d use SUM<\/code>; if \u201caverage,\u201d use AVERAGE<\/code>, and so on. When a formula and its plain-English description diverge, one of them is wrong.<\/li>\n\n\n\n
        4. Avoid hard-coding numbers inside formulas.<\/strong> Enter 0.0825 (an 8.25 % tax rate) in a labelled cell and point to it; don\u2019t scatter 0.0825 inside twenty different formulas. That eliminates hunt-and-replace nightmares.<\/li>\n\n\n\n
        5. Check formulas by switching to formula view<\/strong> (Ctrl<\/code> + “`). Errors jump out when you can see every reference at once.<\/li>\n\n\n\n
        6. Test after every change.<\/strong> As you saw here, fixing H9 instantly healed I9:I23 and G5 without touching them. That feedback\u2010loop tells you the dependency chain is healthy.<\/li>\n\n\n\n
        7. Name your ranges for readability<\/strong> (e.g., TaxRate<\/code>, CostColumn<\/code>). Named ranges would let you write =Cost * TaxRate<\/code>, which reads almost like English and is self-documenting.<\/li>\n<\/ol>\n\n\n\n
          \n\n\n\n
          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\u2019s the power of well-structured formulas: they turn a static table into a resilient financial model that you can trust on event day\u2014and long after.<\/p>\n\n\n\n
          \"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"
          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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[25],"tags":[],"class_list":["post-224033","post","type-post","status-publish","format-standard","hentry","category-exams-certification"],"_links":{"self":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/224033","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/comments?post=224033"}],"version-history":[{"count":0,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/224033\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/media?parent=224033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/categories?post=224033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/tags?post=224033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}