Key Topics & Core Concepts
- Measures of Central Tendency: Arithmetic mean (
AVERAGE), median position (MEDIAN), and multi-mode identification. - Multi-Range Calculations: Non-contiguous range references in AutoSum and subtotal formulas.
- Formula Debugging: Identifying common syntax blunders, incorrect argument boundaries, and circular reference traps.
- Spreadsheet Auditing: Inspecting formula precedents/dependents and toggling Formula View (Ctrl + `).
Assignment Overview & Deliverables
Analyze daily operational metrics, compute descriptive statistical summaries, and audit intentionally flawed formula structures. You will verify non-contiguous column sums, calculate measures of central tendency, compute midrange metrics, and resolve syntax anomalies on the operational dataset.
- Starter File:
homework2-stats.xlsx - Required Submission Name:
[your initials]Homework02_Stats.xlsx - Primary Target Worksheet:
tuesday
Step-by-Step Instructions
Step 1: Open and Save Starter File
- Download and open
homework2-stats.xlsxin Excel. Click Enable Editing if prompted. - Click File > Save As and save the workbook as
[your initials]Homework02_Stats.xlsx. - Ensure the active sheet tab is set to
tuesday(do not modify the template structure or delete tabs).
Step 2: Calculate Multi-Column Total Aggregations
- In the designated column total summary cells, calculate total volume across the primary data columns using
=SUM(...). - In the header grand summary block, compute the total aggregate volume across both non-contiguous data sections.
- Verify that formula ranges match the exact boundary coordinates of the Tuesday operational ledger.
Step 3: Descriptive Statistics Calculations
- Calculate the statistical mean using
=AVERAGE(...)across the relevant volume range. - Calculate the minimum and maximum boundaries using
=MIN(...)and=MAX(...). - Compute the median middle value using
=MEDIAN(...). - Calculate the Midrange value using the formula:
=(MAX(...) + MIN(...)) / 2. Ensure proper parenthetical operator precedence.
Step 4: Formula Debugging & Syntax Correction
- Audit the formula block in cells
H12:H15. Identify and correct the intentional formula errors (e.g., misspelled function tokens, omitted parentheses, or incorrect range boundaries). - Confirm that all summary cells display evaluated numeric values and that no error flags (
#DIV/0!,#NAME?, or#REF!) persist. - Toggle Formula View (Ctrl + `) to visually confirm formula symmetry across rows.
Step 5: Pre-Flight Self-Check & Canvas Submission
- Format all calculated currency values as Currency or Accounting, and format raw unit counts with comma thousands separators.
- Save your finalized workbook (
[your initials]Homework02_Stats.xlsx). - Perform a self-check verification using the autograder pre-flight portal at
/teaching/ISMG-2050/submit.html. - Upload and submit your completed file directly to Canvas prior to the deadline.
Refer to Canvas for official submission cutoff and penalty policies.
Key Skills Evaluated
- Statistical summary functions (
=AVERAGE,=MEDIAN,=MIN,=MAX) - Mathematical operator order of precedence in calculated metrics (Midrange)
- Spreadsheet formula auditing and syntax error resolution
- Multi-range non-contiguous aggregations and accounting format standards