Skip to content
academia.sh
tr
Log in

Course Beginner

Analysis with Spreadsheets

Published Updated

By the end of this course

Start course

01

Basic Operations

Counting how much every cell-level decision moves the number and whether it can be read back from the sheet: the difference relative and absolute references produce when copied and how many cells a wrong reference spreads to, a count function giving three different results for a blank cell, a zero and a number written as text, the records a boundary comparison sends to the wrong tier in nested conditions, the record blocked from matching by an invisible space and a difference in letter case, a day-counting decision giving two durations, the vertical lookup that breaks when a column is inserted and the match-index pair that does not.

  1. 01 Worksheet Structure A single amount formula is copied to 60 rows, and when the reference decision changes the total drops from 13,427.16 fee units to 3,354.98: the relative reference spreads wrongly to 59 cells, 52 of them zero and 7 a wrong number that reads like an amount. The third version, written with an embedded literal, gives the same total, but when the unit rate rises from 8.4 to 9.1 it follows in 0 cells instead of 60. Of the 8 parameters on the sheet, 6 enter no formula.
  2. 02 Basic Functions Four counting functions are applied to a single 60-cell range and four different numbers come out: 53 filled cells, 39 numbers, 29 values above zero, 7 blank cells. The total of the same column is 708.5 m³; because 14 numbers written as text are silently skipped, the real total is 967.2 m³, a difference of 26.7%. The denominator of the mean swings between 11.81 and 24.43 across four defensible decisions, a factor of 2.07; in 1 of six formulas the denominator is not visible at all, in 3 the decision is an embedded literal, and in only 1 is there a reference to a parameter cell.
  3. 03 Logical Functions The same nested condition is applied to the 10/25/40 m³ tariff tiers in three versions: when the boundary comparison is `<` instead of `<=`, 4 records change tier; when the order of the conditions is broken, 20 records fall into the wrong tier and the first tier empties completely. The boundary decision moves the amount total from 26,219.52 to 26,698.32 fee units, that is 1.83%, but it moves a single bill by 35.5%. Of the three versions that produce the same total, only one has the tier written in a cell; in the others the decision sits inside a 3-layer condition and in no cell.
  4. 04 Text Functions 48 field visit records are matched to a 60-subscriber list on the subscriber code, and in raw form only 8 of them match. Trimming raises that to 24, trimming plus uppercase conversion to 32, and removing every kind of whitespace to 48; the consumption reported for the visits swings between 232.1 m³ and 1204.8 m³ across these decisions. A fifth decision that rebuilds the code yields the same 48 while silently attaching a record that arrives with a different prefix to the wrong subscriber. A locale-insensitive case conversion misses 15 of the technician names, and of three versions that produce the same result, only one leaves the 40 correction decisions readable back cell by cell.
  5. 05 Date Functions The mean resolution time of the same 54 support requests comes out as 6.33, 7.33, and 5.20 days under three day-counting decisions; with the endpoints excluded, 6 requests appear to have taken zero days, and in the workday count two requests that took two days on the calendar show zero workdays. What one month later means at month end diverges for 6 of the 60 subscribers: the clamp rule collapses three subscribers onto the same day and produces three periods of 28, 29, and 30 days, while the overflow rule gives every one of them 31 days but shifts the February reading into March. When written as text, 19 of the 54 opening dates can be read as two different days, and the largest deviation is 323 days.
  6. 06 Lookup Functions The same lookup is built in four versions, and when a column is inserted in the middle of the table only one of them breaks: the vertical lookup with an embedded column number returns the wrong value in 48 of 48 records, raises an error in none, and shows a total of 1084.6 m³ instead of 1204.8 m³. Had the inserted column carried an unreasonable number, the same breakage would have shown up as 201081.0 m³. When the table is re-sorted by region, approximate match silently changes 34 records, and the only thing that carries that decision is a zero in the formula.

02

Summary and Cleanup

Measuring which decisions produce the numbers that look at the whole table: how many rows remain and how many records disappear when the deduplication key changes, the three values that imputation and exclusion give for the same mean and the separation of an blank cell from a zero, a pivot table that returns two different winners when the breakdown changes, how many charts do not fit their scale and how many times larger the axis start makes a difference look, and how many of the decisions made in the course can be read back from the table.

  1. 01 Removing Duplicate Records Five deduplication keys applied to the same 2,037-row sheet leave five different counts: 2,005, 1,400, 1,877, 1,965 and 1,877 again. When the key is the subscriber number alone, 637 rows are deleted and the total falls from 42,645.6 m³ to 29,828.2 m³, because most of the deleted rows are second-period readings rather than duplicates; the text trimming decision alone moves 88 rows. Keeping the last entry instead of the first under the same key leaves the row count fixed at 1,877 but pulls the totals 200.1 m³ apart. Once the operation is done, the deleted sheet gives back zero of four decisions, the annotated sheet two in two steps, and the traced sheet four in one step.
  2. 02 Dealing with Missing Data In the reading column of the same 1,877-row sheet 116 cells are blank and 112 cells are zero, and 55 of those zeros are not real zero consumption but an unread visit; the 2,719.2 m³ that corresponds to the blank cells is inside the reported total. The mean of the same column comes out as 19.23, 18.04, 20.54 and 19.23 m³ again under four decisions: 2.49 m³ between the lowest and the highest, while imputation leaves the mean unmoved and raises the total from 33,866.8 to 36,097.6 m³. The blank-cell rate varies between regions from 2.0% to 17.3%, and when blanks are counted as zero the mean of one region falls by 3.94 m³. Once the operation is done, the imputed sheet gives back zero of four decisions, the annotated sheet one in two steps, and the traced sheet four in one step.
  3. 03 Pivot Tables Five summary decisions applied to the same 1,761-row sheet make three different regions come out first: north with 10,905.8 m³ in total consumption, east with 22.80 m³ in the mean per row, central with 28.39 m³ in the mean per subscriber. When the region mean is produced from the cells of the cross tabulation the order holds, but the 3.35 m³ gap between south and west shrinks to 0.04 m³. When the same 20 m³ threshold is applied after the summary three regions pass, and when it is applied before the summary all five pass and the value of the east region rises from 22.80 to 29.03 m³. The pivot table gives two of its four decisions in the header row in one step, and never gives the position of the filter or the level of the calculated field.
  4. 04 Basic Charts Four of ten chart suggestions can be drawn but do not fit the scale of the field or the summability of the measure: a line over a nominal axis, a line over an ordinal axis, a scatter over a field that is a label, and a pie from means. When the same five region means are drawn with two axis baselines, the actual ratio of 1.67 between east and west gives a bar length ratio of 1.63 when the axis starts at zero and 13.00 when it starts at 13 m³; the same difference looks 8.0 times larger and not a single reading changes. The chart reports to the sheet only the range it draws: of four decisions, one can be read back from the charted sheet, two from the annotated sheet and four from the traced sheet.
  5. 05 Limits of Spreadsheets Scanning the 32 decisions made across the course's eleven lessons shows that, as commonly written, only 7 can be read back from the sheet: 6 in one step, 1 in two steps, while 25 appear in no cell. Writing down all of the decisions would take 32 cells, about 3.4 parts per thousand of a sheet with 9,385 data cells. As the sheet grows, a range-reading column becomes 626 times more expensive than a fixed-cost column and quadruples when the rows double; meanwhile a one-row range error shows up in two of four summaries on a 60-row sheet and in none of them on a 1,877-row sheet. Two formula texts fill 7,508 cells: that is the work the tool does cheaply.

Final exam

Analysis with Spreadsheets — Final Exam

In preparation

This course's question bank is not complete yet. The exam opens here automatically once enough questions are added.

Start typing to search.

↑↓ ↵ Esc navigate · open · close