kW vs kWh: The Unit Confusion That Quietly Ruins Solar Spreadsheets

There is a specific bug I have now found in roughly every homemade solar model I’ve been sent. It never announces itself. The spreadsheet opens, the charts render, the payback figure looks plausible, and somewhere in row 40 a number that means “rate of flow” has been added to a number that means “accumulated volume.” The output is wrong by a factor of two, or twelve, or four, and nothing on screen says so.

That’s the whole problem with kW and kWh. They’re not different by a lot. They’re different by a dimension, which is far worse, because dimensional errors produce answers that sit comfortably inside the range you expected.

The distinction, stated in a way that survives contact with a spreadsheet

A kilowatt is a rate. It’s how fast energy is moving right now. Your kettle pulls 3 kW while it’s on. Your 4.2 kWp array might be producing 3.1 kW at 1pm in June and 0.4 kW at 8am in December.

A kilowatt-hour is a quantity. It’s a rate multiplied by a duration. The kettle at 3 kW for six minutes (0.1 h) consumes 0.3 kWh. That’s it. That’s the entire relationship:

kWh = kW × hours
kW  = kWh ÷ hours

Here’s the part that trips people up, and it’s a language problem rather than a physics one. British electricity billing is denominated in energy (p/kWh), British appliance labelling is denominated in power (W), British solar quoting is denominated in peak power (kWp), British battery marketing uses both and often doesn’t say which (a “5 kW battery” is meaningless; a Givenergy 9.5 kWh battery with a 5 kW inverter is two facts), and the half-hourly settlement data your supplier hands you is energy-per-interval. Five conventions, one column of numbers you’re about to paste into Excel.

The error, with real numbers

Say you export a CSV from Octopus (via their API or Octopus Watch), and you get half-hourly consumption. The column header says consumption. The values look like this:

interval_start          consumption
2026-01-14T18:00:00Z    0.612
2026-01-14T18:30:00Z    0.703
2026-01-14T19:00:00Z    0.588

Those are kWh per half-hour. Not kW. If you sum the day you get your daily kWh, correctly. But if you look at 0.703 and think “so we’re pulling 0.7 kW at 6:30pm,” you are wrong by exactly 2×. The actual average power over that interval is 0.703 kWh ÷ 0.5 h = 1.406 kW.

Now watch what that does downstream. You’re sizing a battery for evening load shifting. Evening peak 4pm to 10pm, six hours. You read the half-hourly values, average them to about 0.65, multiply by 6 hours, get 3.9 kWh, and conclude a 5.2 kWh battery comfortably covers your evening. The real answer is 7.8 kWh of evening consumption, so the 5.2 kWh unit covers about two-thirds of it and you will still be importing at peak rates every single night. Same input data. One missing ÷ 0.5.

The reverse error is just as common and shows up in generation modelling. PVGIS returns monthly and annual energy in kWh, but its hourly radiation endpoint returns W/m². Someone pulls hourly output in W, forgets to divide by 1000, and their 4.2 kWp array generates 3,700,000 kWh a year. That one you catch, because it’s absurd. The dangerous version is subtler: pulling PVGIS E_m (monthly kWh) and comparing it against a Solar Edge or Solis inverter’s instantaneous power reading, then “adjusting” the model because the numbers don’t agree. They were never going to agree. They aren’t the same kind of number.

A third one, specific to UK tariffs. Standing charge is p/day. Unit rate is p/kWh. Export payment under SEG is p/kWh. Inverter clipping limits are kW. If your model has a single rate column, at least one of those four is in the wrong units and your payback year is fiction.

The convention: put the unit in the name, always

Here’s the discipline. It takes ten minutes to adopt and it eliminates the entire error class.

Every column header carries its unit as a suffix. No exceptions, not even for “obvious” ones.

Ugly? Yes. Verbose in formulas? Also yes. But now =SUM(ac_power_kw) looks visibly wrong when you type it, because you can read the units out loud and hear that summing a rate gives you nonsense. That’s the point. You want the mistake to be legible, not hidden behind a header that says output.

Second rule: never let two different units share a column. If you have half-hourly data and daily totals in the same sheet, they live in separate tables. The temptation to stack them “because it’s all consumption” is how a 30-minute 0.7 gets averaged in with a daily 18.4.

Third rule: build a unit-check row. At the top of every calculation block, one row of assertions that break loudly:

=IF(AND(MAX(gen_kwh_per_30min) < array_size_kwp * 0.5 * 1.15,
        MAX(gen_kwh_per_30min) > 0),
    "OK", "UNIT ERROR: half-hourly energy exceeds physical ceiling")

A 4.2 kWp array cannot produce more than about 2.1 kWh in half an hour, and in the UK it realistically won’t exceed about 1.9 kWh. If a value in that column reads 3.8, you’re holding power not energy, and the cell says so in red. Build the equivalent for consumption using your main fuse rating: an 80 A single-phase supply at 230 V caps you at roughly 18.4 kW, so 9.2 kWh per half hour is your hard physical ceiling. Anything above it is a units bug, full stop.

Fourth rule: one conversion layer, at the edge. Raw imported data keeps its native units and its original name, untouched, in a raw_ sheet. Conversions happen once, in a single clearly labelled block, and everything downstream reads only the converted columns. If you find yourself dividing by 0.5 in three different places, you’ve already lost track.

Making AI tools declare units before they calculate

This is where most people get burned twice, because LLMs are fluent about kW and kWh and will happily produce a confident, dimensionally incoherent answer. Ask ChatGPT or Claude “how big a battery do I need if my evening usage is 0.65?” and you’ll get a number. It won’t ask what 0.65 means. It’ll pick an interpretation silently.

So force the declaration. This prompt pattern has saved me more than anything else:

Before performing any calculation, output a units table listing every quantity I’ve given you, with: the quantity name, the numeric value, the unit you believe it is in, and your confidence. Where a value is ambiguous between power and energy, say so explicitly and stop. Do not calculate until I confirm the table.

The “stop and wait” clause matters. Without it, models will produce the table and then proceed on their own assumption, which defeats the purpose.

For data pipeline work, a stronger version:

You are reviewing a solar spreadsheet for dimensional errors. Treat kW and kWh as incompatible types that cannot be added, subtracted or compared. For each formula, state the unit of every input and the unit of the result. Flag any formula where: a kW value is summed, a kWh value is treated as a rate, a per-30-minute energy figure is used without a ÷0.5 conversion to power, or a currency rate in p/kWh multiplies something that isn’t kWh. Report only the flagged formulas.

And when you’re going the other direction, asking an AI to generate the model:

Every column you create must have a unit suffix in its header, following the pattern name_unit_per_interval. Include a validation row that flags physically impossible values against a 4.2 kWp array on an 80 A single-phase supply. If you need a value I haven’t given, list it as a named input cell rather than hardcoding an assumption.

That last clause is a separate lifesaver. Hardcoded 0.85 inverter efficiencies buried inside a formula are how models become unauditable. If you’re building out a wider set of these prompts and the pipelines that feed them, the AI tools and prompts pillar goes deeper on structuring the whole chain from API pull to validated sheet.

A worked example, end to end

4.2 kWp array, east-west split, Sheffield. Givenergy 9.5 kWh battery, 5 kW hybrid inverter. Octopus Go, 8.5p/kWh overnight and 27.4p/kWh day, standing charge 52p/day. SEG export at 15p/kWh.

Annual generation from PVGIS: 3,540 kWh. That’s energy, for the year. Divide by 8,760 hours and you get an average of 0.40 kW, which tells you almost nothing useful but is at least dimensionally honest.

Battery throughput: 9.5 kWh usable, cycled once a day, 365 days, at 90% round-trip = 3,120 kWh shifted per year. The 5 kW inverter rating never enters this calculation. It constrains how fast you can fill or empty the battery, which matters on a January morning when you want a full charge inside a four-hour Go window (9.5 kWh ÷ 4 h = 2.4 kW required, comfortably under 5 kW, so fine) but it does not appear anywhere in the annual energy sum.

Savings: 3,120 kWh × (27.4 − 8.5)p = £589.68 from arbitrage. Self-consumed solar, say 1,400 kWh × 27.4p = £383.60. Exported 2,140 kWh × 15p = £321.00.

Notice that every single multiplication has kWh on one side and p/kWh on the other. That’s the check. If you ever find yourself multiplying a kW figure by a p/kWh rate, the result is pounds-per-hour of something, and you have built a nonsense.

Where to look first when a model smells wrong

Factor-of-two errors mean half-hourly data treated as power, or vice versa. Factor of 48 means half-hourly summed as if daily. Factor of 1000 means W and kW got crossed, usually at an API boundary. Factor of 24 means a daily figure used as hourly.

Those four ratios cover the overwhelming majority of what I see. When your payback comes out at 3.1 years and your gut says 9, divide one by the other and see whether the answer is suspiciously close to 2, 24, 48, or 1000. It usually is, and that number tells you exactly which conversion you skipped.

The version of this discipline that actually sticks isn’t the one where you’re careful. Careful fails at 11pm when you’re pasting a new CSV in. It’s the one where the sheet refuses to give you a plausible-looking answer from incoherent inputs, because a validation cell three rows up has gone red and said, in words, that a 4.2 kWp array cannot make 3.8 kWh in thirty minutes.