Calligra Sheets · Practical projects · Intermediate
Check a Full-Tank Fuel Economy Log in Calligra Sheets
Calculate full-to-full fuel use from odometer readings, exclude the first incomplete interval and aggregate litres over matching distance.
KDE handbook reviewed; independent arithmetic checked; native verification pending · Editorial standards
Fuel bought at a fill belongs to the distance since the preceding comparable full fill, not the distance that comes afterward. Build a small full-to-full log and calculate an overall litres-per-100-kilometres rate without averaging unequal intervals.
This uses fictional readings and elementary measurement arithmetic. It is not a vehicle-efficiency certification or a recommendation to change driving or refueling practices.
Scope and measurement boundary
Use free Calligra Sheets desktop on Linux/KDE. The official English handbook hosted at the KF6 documentation URL identifies itself as revision 2.7, dated 11 March 2013. This page uses its documented arithmetic, SUM, COUNT and IF behavior; it does not claim that the old handbook proves a specific current release’s interface. English formulas use semicolon separators. Native execution, current-version menus and file persistence have not been tested for this exercise.
The fixture assumes:
- One vehicle, kilometres for every odometer reading, litres for every fuel amount.
- Each listed visit finishes at a comparable full-tank level, following the vehicle’s normal safe filling instructions; do not overfill to force a measurement.
- Every fill between opening and closing full-tank readings is recorded; no unlisted partial fills, fuel transfers, odometer resets or unit changes.
- Odometer readings increase, and each measured interval has positive distance and positive fuel use.
The spreadsheet cannot prove these assumptions from numeric entries. If they are false, stop and rebuild comparable intervals from the original records.
Record the opening boundary separately
Create a blank workbook. Enter this table in A1:C5. Visit names are labels, not dates, avoiding locale-dependent date entry.
| Row | A: Visit | B: Odometer km | C: Litres added |
|---|---|---|---|
| 2 | Opening full fill | 12000 | 35 |
| 3 | Full fill 2 | 12400 | 28 |
| 4 | Full fill 3 | 13000 | 48 |
| 5 | Full fill 4 | 13300 | 27 |
The 35 litres in C2 must not enter this period’s consumption total. It brings the vehicle to the opening boundary; the preceding distance is unknown. Keep it in the raw log for traceability, but leave D2:F2 empty.
Put Input check, Interval km, and L/100 km in D1:F1.
Check each complete interval
In D3 enter and copy through D5:
=IF(COUNT(B2:B3)<>2;"CHECK ODOMETER";IF(COUNT(C3)<>1;"CHECK FUEL";IF(B3<=B2;"CHECK DISTANCE";IF(C3<=0;"CHECK FUEL";"OK"))))
In E3 enter and copy through E5:
=IF(D3="OK";B3-B2;"")
In F3 enter and copy through F5:
=IF(D3="OK";100*C3/E3;"")
Each formula starts with =. Enter it into the indicated cell, then copy to the remaining rows in its column. Relative row references connect each reading to the preceding reading; inspect the last distance formula to ensure it uses B5−B4, not B5−B2.
| Row | Expected input check D | Interval km E | L/100 km F |
|---|---|---|---|
| 3 | OK | 400 | 7.00 |
| 4 | OK | 600 | 8.00 |
| 5 | OK | 300 | 9.00 |
Use an ordinary numeric display with two decimal places for F. Keep the unit in the heading; do not type 28 L or 12400 km into input cells. Review number formatting if the result is displayed unexpectedly.
Aggregate matching fuel and distance
Put these labels in H2:H5 and formulas in I2:I5:
| Label | Formula | Expected |
|---|---|---|
| Measured distance km | =IF(COUNT(E3:E5)<>3;"CHECK INTERVALS";SUM(E3:E5)) |
1300 |
| Measured fuel litres | =IF(COUNT(E3:E5)<>3;"CHECK INTERVALS";SUM(C3:C5)) |
103 |
| Overall L/100 km | =IF(COUNT(E3:E5)<>3;"CHECK INTERVALS";100*SUM(C3:C5)/SUM(E3:E5)) |
7.92 displayed |
| Distance reconciliation km | =IF(COUNT(E3:E5)<>3;"CHECK INTERVALS";SUM(E3:E5)-(B5-B2)) |
0 |
The unrounded overall rate is 103 ÷ 1,300 × 100 = 7.923076923… L/100 km. The arithmetic average of the three interval rates is 8.00, which is not the same: the 7, 8 and 9 rates cover different distances. Use total fuel divided by matching total distance, not the average of the rate column.
A zero reconciliation proves only that adjacent distances add to the boundary difference. It cannot detect an omitted fuel purchase or a wrong intermediate reading that still preserves the boundaries.
Test the boundary and error handling
Save a checkpoint. Try each change separately, restoring it before the next:
- Change C2 from 35 to 40. No calculated interval or overall rate changes, because the opening fill is excluded.
- Change C5 from 27 to 24. The last interval rate becomes 8.00, measured fuel 100, and the overall rate 7.692307692…, displayed as 7.69.
- Set B4 equal to B3, 12400. D4 must show CHECK DISTANCE, E4 and F4 display empty, and all four summary cells show CHECK INTERVALS. D5 may still be OK, but the period must not be presented as complete.
- Clear C4 or replace it with text
unknown. D4 must show CHECK FUEL, and the summary remains blocked. - Set C4 to 0. A zero fill is not a usable complete interval under this model: expect CHECK FUEL.
Restore the original inputs and check 1,300 km, 103 litres and 7.92 displayed. Missing data must not disappear from a SUM and leave a plausible partial overall rate.
Handle a partial fill without inventing a rate
Do not mark a partial fill as a full boundary. If a known partial fill occurs between verified full fills, preserve individual receipts in a separate raw log. Combine all litres bought after the starting full fill through the ending full fill, using the odometer difference between those full boundaries.
For example, if the 600 km interval had a 20-litre partial fill followed by 28 litres at its ending full fill, its measured fuel remains 48 litres and its rate remains 8.00. Do not calculate a full-to-full rate at the partial visit. The formulas above expect already-established complete boundaries; they do not automate partial-fill grouping.
Save and troubleshoot
Save a new OpenDocument Spreadsheet (.ods) file, reopen it and rerun the changed-fuel check yourself. Keep raw receipts separate from calculated intervals. Adding another full fill requires copying D:F and extending every summary range and the interval count of three together.
If edits do not change calculations, inspect recalculation settings. If a formula is displayed literally, re-enter it in a normal numeric/general cell rather than typing the answer. If a total is too high, check whether C2 was accidentally included. If rates vary dramatically, first check units, boundary assumptions and recording errors; do not assume the difference is mechanically meaningful.
Sources and evidence limits
The official handbook revision, basic arithmetic and recalculation, formula references, and function reference support the spreadsheet techniques. The full-to-full fixture, unit relationships and changed-case expectations are independent arithmetic, not an official KDE vehicle workflow. No native execution, screenshots, vehicle measurement or current-release compatibility test is claimed.