Multiplying a total by an average can look plausible and still be wrong. Spreadsheet gives you a way to do the arithmetic per geometry instead, and to control how the result rolls up, so a mixed scheme totals correctly instead of just plausibly.
Per-geometry arithmetic
GI.TOTAL("area") * GI.WEIGHTEDAVG("hardCost") multiplies a total by an average rate, which is only correct if every geometry shares that exact rate. On a mixed scheme this can read 17% higher than the true number.
Do the arithmetic per geometry, then add the results up, with GI.SUMEACH:
=GI.SUMEACH("area * hardCost")=GI.SUMEACH("area * salePrice / levels", "usage=Residential")
GI.SUMEACH works the expression out against every matching geometry and adds up the results. GI.AVGEACH, GI.MINEACH and GI.MAXEACH do the same loop, reduced differently, and GI.EACH(expression, n) gives one geometry's answer, for when the working has to show down a column.
The expression language covers numbers, field names, + - * / ^ %, parentheses, and MIN MAX ABS ROUND SQRT IF. Wrap a field name in brackets when it contains anything unusual, like [dwelling-1BED-Count].
A geometry missing a field is skipped, not counted as zero
A zero would claim that geometry costs nothing. Instead, a geometry missing a field used in a per-geometry formula is skipped and contributes nothing to the result. GI.AVGEACH only divides by the geometries that actually contributed, so a skip never drags the average down the way a false zero would.
The cell still tells you when this happens: an amber underline, a tooltip, and a warning in the status bar, for example "11 of 40 skipped: no hardCost." If every geometry is skipped, GI.SUMEACH reads 0, the sum of nothing, while GI.AVGEACH reads #DIV/0!, because the average of nothing has no answer.
Measure classes: why some numbers refuse to add up
Every field Spreadsheet reads from the project is classified before anything aggregates it, because summing the wrong kind of field produces a wrong answer that looks completely plausible:
Plan fields, like
area, sum directly. They are one level's worth per geometry.Total fields, like
grossAreaor a dwelling count, also sum directly. The number of levels is already multiplied in.Rate fields, like
salePriceorhardCost, average instead of summing, weighted by floor area. An unrecognised field defaults to this class, because a field that refuses to add up when it should not is visible and correctable, while one that adds up when it should not is a wrong number nobody notices.Nested fields, like a usage's dwelling mix, show as JSON and are never totalled.
Spreadsheet will still sum a rate field if you ask it to. The column marks itself amber, the cell's tooltip says why, and the status bar counts it. Auto never does this on its own.
Each column in the Giraffe data pane shows which class is in force, whether it was inferred or set by hand, and a dropdown to correct it. A correction travels with the workbook, so you only make it once.
Averages of rates are weighted by floor area
GI.WEIGHTEDAVG(field) is the mean weighted by floor area, not a plain average. Adding $8,700/m² to $6,000/m² gives $14,700/m², which is not a price, so a per-unit rate has to reduce by averaging. Weighting by floor area rather than footprint matters too: footprint weighting would under-count a nine-storey tower ninefold against a single-storey building on the same ground.
Site area is not footprint
GI.SITEAREA() is the area of the project boundary. GI.FOOTPRINT() is the ground the geometries cover, one level each. A plot ratio built on footprint instead of site area reads higher than the true number, by however much of the site is not built on.