Spreadsheets in practice

Work through Prairie Town’s real day in a spreadsheet: formulas, fill-down, absolute references and a check cell

Math toolbox K7 of Energy Systems, Step by Step: in a working spreadsheet of Prairie Town’s real day, use cell addresses, formulas, fill-down, relative and absolute references, SUM and MAX, a what-if and a check cell, then practise on five tasks.
  • Math toolbox
  • 20 min
  • No prerequisites

Prairie Town's day in a sheet, with the totals

One row per hour of 3 September 2025, so a row's MW for that hour is also its MWh.

Row Hour Demand, MW Wind, MW Solar, MW Gas needed, MW Gas cost, $
5 00:00 250 30 0 120 =B5-C5-D5-$E$1 4,800 =E5*$E$2
6 01:00 243 28 0 115 4,600
7 02:00 238 28 0 110 4,400
8 03:00 235 30 0 105 4,200
9 04:00 236 36 0 100 4,000
10 05:00 237 42 0 95 3,800
11 06:00 253 43 0 110 4,400
12 07:00 260 48 7 105 4,200
13 08:00 266 46 70 50 2,000
14 09:00 278 35 118 25 1,000
15 10:00 286 36 130 20 800
16 11:00 300 38 147 15 600
17 12:00 311 35 156 20 800
18 13:00 327 33 149 45 1,800
19 14:00 340 33 112 95 3,800
20 15:00 344 37 147 60 2,400
21 16:00 353 47 161 45 1,800
22 17:00 349 54 130 65 2,600
23 18:00 344 59 70 115 4,600
24 19:00 331 72 14 145 5,800
25 20:00 319 89 0 130 5,200
26 21:00 303 98 0 105 4,200
27 22:00 285 95 0 90 3,600
28 23:00 269 89 0 80 3,200
30 Day total: gas energy (MWh) and cost ($) 1,965 MWh =SUM(E5:E28) $78,600 =SUM(F5:F28)
31 Peak hour: gas output (MW) 145 MW =MAX(E5:E28)

E1 holds the nuclear plant's steady output: 100 MW. E2 holds the gas cost: $40/MWh.

With JavaScript on, you build these formulas yourself in a working sheet.

Demand, wind and solar follow the real hourly shapes of 3 September 2025 on the Southwest Power Pool grid (EIA-930), scaled to Prairie Town as in lesson 0.

Units and assumptions (illustrative)
Item Value What it means
Units MW, MWh MW is power, the average over each hour; MWh is energy, MW held for one hour.
E1 100 MW The nuclear plant's steady output, as in lesson 0. Fixed in this sheet.
E2 $40/MWh Running cost of the gas combined-cycle plant. You change it in step 7.
Left out – How fast the gas plant can change its output (its ramp rate).

What you just did, as diagrams

Address
A column letter and a row number, such as B24; formulas point at addresses, not at numbers.
Relative address
B5 becomes B6 when filled down one row: right for data that changes row by row.
Absolute address
$E$1 never moves: right for one input, such as a price.
Range and function
SUM(F5:F28) adds every cell from F5 to F28; MAX, MIN and AVERAGE work the same way.
Check cell
A total computed two ways, which must agree: F30 against E30 × E2. Agreement shows the sheet is consistent, not that its inputs are right.
Go deeper · AnalystThe same column in Python

A spreadsheet column with one formula filled down is what a data table calls a vectorised operation: one line acts on every row at once. With the day in a pandas table df and the inputs as plain variables:

nuclear = 100                                              # E1: MW, runs flat
gas_cost = 40                                              # E2: $/MWh
df["gas_mw"] = df["demand"] - df["wind"] - df["solar"] - nuclear
df["gas_cost"] = df["gas_mw"] * 1 * gas_cost               # MW × 1 h × $/MWh
df["gas_cost"].sum(), df["gas_mw"].max()                   # F30 and E31

No addresses and no fill-down, so no sliding E1. Bridge B2 does this with a whole year of real hourly data.

Go deeper · ModelerWhen to leave the spreadsheet
  • Size: a year of hours is 8,760 rows per column; many plants or regions multiply that quickly.
  • Checking: a formula hidden in one cell is easy to break and hard to review; code can be read, tested and versioned (toolbox K8).
  • Optimization: a sheet computes what a plan costs; choosing the best plan needs a solver (bridge B1).
  • Still useful: quick estimates, a check of a model's totals, and tables to share. Many studies keep a sheet for exactly that.

Check yourself

Myth or fact? “Typing 120 into E5 is as good as the formula, as long as the number is right.”

  • Myth
  • Fact
Answer

Myth. A typed number does not follow its inputs: change the demand or the nuclear output and E5 silently goes wrong.

Myth or fact? “=E5*$E$2 filled down to row 6 becomes =E6*$E$2.”

  • Myth
  • Fact
Answer

Fact. E5 is relative and moves one row; $E$2 is absolute and stays.

A formula in B10 reads =B5*$A10. Filled right to C10, it becomes…

  • =B5*$A10
  • =C5*$A10
  • =C5*$B10
  • =B5*$B10
Answer

=C5*$A10: B5 moves one column; $A10 has its column fixed, so it stays in column A.

Each row is one hour. What unit does =SUM(E5:E28) have, if column E is in MW?

  • MW
  • MW per hour
  • MWh, because each row lasts 1 h
  • $
Answer

MWh: each row's MW lasts one hour, so each adds MW × 1 h. With 15-minute rows you would multiply the sum by 0.25 h.

Practice sheet

Use the table of the day above. With JavaScript on, you type each answer as a formula in a working sheet (about 30 minutes).

  1. Total demand energy over the day, in MWh (each row is one hour).
    Answer=SUM(B5:B28) = 6,957 MWh
  2. Share of the day's demand met by wind and solar, in %.
    Answer=SUM(C5:D28)/SUM(B5:B28)*100 = 37.3%
  3. Average gas need over the five hourly rows labelled 17:00 to 21:00, in MW.
    Answer=AVERAGE(E22:E26) = 112 MW
  4. The day's highest solar output, in MW.
    Answer=MAX(D5:D28) = 161 MW
  5. The day's gas cost if gas cost $55 per MWh, without changing E2.
    Answer=E30*55 = $108,075

Habits, sources and what’s next

Symbols to remember

Symbol Meaning Example from this page
= starts a formula =B5-C5-D5-$E$1 in E5
$ pins the column or row that follows it $E$1: the nuclear input, in every row
: a range, from one cell to another SUM(F5:F28): the day's gas cost

Next

B2 · Python and data for energy: how do you load, plot and check a year of hourly data?

Sources and further reading

Spotted an error, or stuck? Write to hlli@ksu.edu. Corrections are made in place and dated here.

Text licensed CC BY 4.0, code MIT: teachers and students may reuse and adapt it, with credit to Honglin Li.