Function

Parts

Example

Why

GI.TOTAL(field, [filter], [source])

field is the property to add up, e.g. "grossArea".

=GI.TOTAL("grossArea", "usage=Residential")

the total gross floor area for one usage, feeding straight into a GFA line on a pro forma.

GI.SUM(field, [filter], [source])

identical to GI.TOTAL; it is the same function under the name people reach for first.

=GI.SUM("dwellingTotalCount")

total unit count across the whole scheme, for a unit-count summary line.

GI.WEIGHTEDAVG(field, [filter], [source])

field is the rate to average, weighted by each geometry's floor area.

=GI.WEIGHTEDAVG("salePrice", "usage=Residential")

a blended sale rate across a mixed building, the number a valuer would actually quote rather than a plain average that treats a penthouse and a studio as equally important.

GI.AVG(field, [filter], [source])

field is the property to average, unweighted, one geometry one vote.

=GI.AVG("levels")

average building height in storeys across a masterplan, for a massing summary.

GI.MIN(field, [filter], [source]) / GI.MAX(field, [filter], [source])

field is the property to compare.

=GI.MAX("levels")

the tallest building on site, for a height-limit compliance check; GI.MIN the same for a minimum-height planning condition.

GI.FOOTPRINT([filter], [source])

no field argument. It always reads ground coverage, one level per geometry.

=GI.FOOTPRINT("usage=Retail")

ground floor coverage for a site-coverage or setback compliance check.

GI.SITEAREA()

no arguments.

=GI.TOTAL("grossArea") / GI.SITEAREA()

the site area a plot ratio (FAR) divides by. It is not the same as footprint.

GI.UNITS()

no arguments. Returns "metric" or "imperial".

="Areas in "&IF(GI.UNITS()="imperial","ft²","m²")

label a schedule's header correctly whichever unit system the project runs in, so a report never misstates its own units.

GI.AREA([filter], [source])

identical to GI.FOOTPRINT; kept as the older name so an existing sheet does not break.

=GI.AREA()

same as GI.FOOTPRINT above.

GI.COUNT([filter], [source])

no field argument, only filter and source.

=GI.COUNT("usage=Residential")

how many residential buildings exist, for a building-count line in a summary table.

GI.COUNTUNIQUE(field, [filter], [source])

field is the property whose distinct values are counted.

=GI.COUNTUNIQUE("usage")

how many distinct usages a masterplan has, a quick mixed-use diversity check.

GI.WHERE(field, value, [field2, value2, …])

field/value pairs. Each field is a property name, optionally ending in an operator (= != > < >= <= ~); each value is what to compare it to, usually a cell reference. No operator means equals.

=GI.TOTAL("grossArea", GI.WHERE("usage", A2))

a schedule row that re-filters itself to whatever usage is typed in a cell, without writing the quote-and-& syntax a hand-built filter needs.

GI.SUMEACH(expression, [filter], [source])

expression is text in a small formula language of field names, + - * / ^ %, parentheses and MIN MAX ABS ROUND SQRT IF, worked out separately for each geometry and then added up.

=GI.SUMEACH("grossArea * hardCost")

true construction cost across a mixed scheme where the rate per m² actually differs by building. GI.TOTAL("grossArea") * GI.WEIGHTEDAVG("hardCost") would multiply a total by an average instead, and read wrong the moment the rate is not uniform.

GI.AVGEACH(expression, [filter], [source])

same as GI.SUMEACH, averaged across the geometries that had every field the expression needs.

=GI.AVGEACH("grossArea / levels")

average floorplate size per level, a genuine per-building average rather than one computed from two totals.

GI.MINEACH(expression, [filter], [source]) / GI.MAXEACH(expression, [filter], [source])

same as GI.SUMEACH, reduced to the smallest or largest result instead.

=GI.MINEACH("grossArea / levels")

the smallest floorplate per level across the scheme, useful for spotting an inefficient tower before it reaches a report.

GI.EACH(expression, index, [filter], [source])

expression as above; index is which matching geometry to read, 1 for the first. Fill down with ROW()-1 (from row 2) to get one row per geometry.

=GI.EACH("grossArea * hardCost", ROW()-1)

the per-geometry loop written out down a column, for when a total is not enough and the underlying working has to be visible in a report.

GI.SUMPRODUCT(fieldA, fieldB, [filter], [source])

fieldA and fieldB are the two properties to multiply together, geometry by geometry, before summing.

=GI.SUMPRODUCT("area", "hardCost")

the two-field shorthand for exactly the case GI.SUMEACH("area * hardCost") covers, when the expression is just one multiplication.

GI.NTH(field, index, [filter], [source])

field is the property to read; index is which matching geometry, 1-based. Past the last match it returns blank.

=GI.NTH("grossArea", ROW()-1)

fill down beside GI.NTH("name", ROW()-1) to build a one-row-per-building schedule with ordinary spreadsheet arithmetic between the columns.

GI.LIST(field, index, [filter], [source])

field is the property whose distinct values are listed; index picks the nth one, 1-based.

=GI.LIST("usage", ROW()-1)

fill down to build the row labels of a usage-by-usage schedule automatically, rather than typing each usage name by hand.

GI.FIELDS(index, [source])

index picks the nth property name the source carries, 1-based. No field or filter argument.

=GI.FIELDS(ROW())

fill down to see every field name a project actually carries, when building a new schedule against an unfamiliar project.

GI.CLASS(field, [source])

field is the property to classify. No filter argument.

=GI.CLASS("grossArea")

confirm how Spreadsheet is aggregating a field before trusting a total built on it.

GI.VALUE(field, filter, [source])

field is the property to read off the first matching geometry; filter narrows which one that is.

=GI.VALUE("levels", "usage=Retail")

pull a single specific value, such as one usage's floor-to-floor height, into an assumption cell.

Function

Parts

Example

Why

SUM(number1, [number2], …) / AVERAGE(number1, …)

one or more numbers or ranges.

=SUM(B2:B40)

add a column of hand-typed cost assumptions, or average a column of comparable sale prices you pasted in from a market report.

MIN(number1, …) / MAX(number1, …)

one or more numbers or ranges.

=MAX(C2:C24)

the highest bid in a column of offers, or the earliest handover date in a construction programme.

COUNT(value1, …) / COUNTA(value1, …)

one or more values or ranges. COUNT counts only numbers; COUNTA counts anything that is not blank.

=COUNTA(B2:B40)

how many rows of a hand-entered unit schedule have been filled in so far.

COUNTIF(range, criteria) / SUMIF(range, criteria, [sum_range])

range is what is tested; criteria is the condition, e.g. "Residential" or ">5000"; sum_range is what to add when it differs from range.

=SUMIF(B2:B40, "Residential", D2:D40)

total the area column only where a hand-typed usage column reads "Residential": the ordinary-spreadsheet equivalent of a filtered GI.TOTAL, useful once data has come in from outside the project.

COUNTIFS(range1, criteria1, …) / SUMIFS(sum_range, range1, criteria1, …) / AVERAGEIFS(average_range, range1, criteria1, …)

repeating range/criteria pairs, one per condition, every one of which must be true. Note that SUMIFS puts the range to sum first, while SUMIF puts it last.

=SUMIFS(D2:D40, B2:B40, "Residential", C2:C40, ">4")

total area for residential buildings that are also over four storeys, when both columns were typed in rather than read live from the project.

MEDIAN(number1, …)

one or more numbers or ranges.

=MEDIAN(E2:E30)

the median sale price in a column of comparable transactions, less skewed by one outlier sale than the average would be.

SUMPRODUCT(array1, [array2], …) / PRODUCT(number1, …)

SUMPRODUCT takes two or more equal-sized ranges and multiplies them position by position before adding; PRODUCT just multiplies numbers together.

=SUMPRODUCT(B2:B10, C2:C10)

the ordinary-spreadsheet version of GI.SUMPRODUCT, for a unit count column times a rent column typed in from a rent roll rather than read from the project.

ROUND(number, digits) / ROUNDUP(number, digits) / ROUNDDOWN(number, digits)

number is the value; digits is how many decimal places to keep. ROUND rounds to the nearest, ROUNDUP always away from zero, ROUNDDOWN always toward zero.

=ROUNDUP(GI.TOTAL("dwellingTotalCount")/2, 0)

round a car-parking-ratio calculation up to a whole space, since a planning condition never accepts half a space.

ABS(number) / INT(number) / MOD(number, divisor) / SQRT(number) / POWER(number, power)

ABS and SQRT take one number; INT rounds one number down to an integer; MOD takes a number and a divisor; POWER takes a number and the power to raise it to.

=ABS(revenue - cost)

a variance figure that should read the same whether a budget line is over or under, without an extra IF to flip the sign.

Function

Parts

Example

Why

IF(test, if_true, [if_false])

test is a condition; if_true/if_false are what to return for each case.

=IF(GI.TOTAL("dwellingTotalCount")>200, "Needs a traffic study", "")

flag a schedule row automatically once a live count crosses a planning threshold.

IFS(test1, value1, …)

repeating test/value pairs; the first true test wins.

=IFS(levels<=4,"Low-rise", levels<=12,"Mid-rise", TRUE,"High-rise")

classify a building into a height band for a summary table, without nesting several IFs inside each other.

IFERROR(value, if_error)

value is the formula to try; if_error is what to show instead of an error.

=IFERROR(GI.WEIGHTEDAVG("efficiency","usage=Retail"), "no retail")

show a plain note instead of a formula error on a schedule row for a usage the project does not have yet.

SWITCH(value, case1, result1, …, [default])

value is compared against each case in turn; the matching result is returned, or default if none match.

=SWITCH(A2, "Residential", 1.0, "Retail", 1.5, "Office", 1.2, 1.0)

look up a car-parking ratio or a cost loading by usage name without a separate lookup table.

CHOOSE(index, value1, …)

index picks which of the listed values to return.

=CHOOSE(MONTH(A2), "Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")

map a draw-schedule date to the financial quarter it falls in.

AND(logical1, …) / OR(logical1, …) / NOT(logical)

one or more true/false conditions.

=IF(AND(levels>10, GI.CLASS("hardCost")="rate"), "check cost rate", "")

combine two planning or cost conditions into one flag rather than nesting IFs.

Function

Parts

Example

Why

VLOOKUP(value, table, col_index, [exact])

value is what to find; table is the range to search, matched against its first column; col_index is which column of it to return, 1 for the first; exact set to FALSE requires an exact match rather than a nearest one.

=VLOOKUP(A2, $H$2:$J$20, 3, FALSE)

look a usage's parking ratio up from a separate assumptions table kept on another sheet.

INDEX(array, row, [col]) / MATCH(value, array, [type])

MATCH finds value's position in array; INDEX then returns whatever sits at that row (and col) of another range.

=INDEX($D$2:$D$40, MATCH(A2, $B$2:$B$40, 0))

the same lookup as VLOOKUP, without requiring the answer column to sit to the right of the one being searched.

ROW([reference]) / COLUMN([reference])

an optional cell reference; the current cell if left out.

=GI.NTH("name", ROW()-1)

drive the index argument of GI.NTH, GI.LIST or GI.EACH so a formula filled down a column advances automatically.

Function

Parts

Example

Why

CONCAT(text1, …) / TEXT(value, format)

CONCAT joins any number of text values; TEXT formats one number as text using a format string.

=CONCAT("Level ", levels)

build a readable label, such as a level name, out of live project numbers.

LEFT(text, [count]) / RIGHT(text, [count]) / MID(text, start, count)

text is the source string; count is how many characters to take, from the left, right, or starting position; start is where MID begins counting.

=LEFT(A2, 3)

pull a lot or parcel code's project prefix out of a longer reference number pasted from a title document.

LEN(text) / TRIM(text)

text is the string to measure or clean.

=TRIM(A2)

strip stray spaces from an address or usage name pasted in from an external spreadsheet, so it matches the project's own spelling exactly.

UPPER(text) / LOWER(text)

text is the string to convert.

=UPPER(A2)

normalise a usage name typed inconsistently by different people before comparing it against another sheet.

Function

Parts

Example

Why

TODAY() / DATE(year, month, day)

TODAY takes nothing; DATE takes a year, month and day.

=DATE(2026, 1, 1)

set a fixed valuation or programme start date that stays constant even as TODAY() moves on.

YEAR(date) / MONTH(date) / DAY(date)

date is a date cell.

=YEAR(A2)

group a cashflow's monthly rows into financial years for a summary total.

EDATE(date, months) / EOMONTH(date, months)

date is the starting date; months is how many months forward (or back, if negative) to move.

=EOMONTH(A2, 0)

build a monthly cashflow's date column so each row lands on the actual end of that calendar month, not a fixed number of days later.

DAYS(end, start)

end and start are two dates.

=DAYS(handover_date, contract_date)

a construction programme's duration in days, straight from a contract date and a projected handover date.

YEARFRAC(start, end, [basis])

start and end are two dates; basis picks the day-count convention (0 for 30/360, the default).

=YEARFRAC(loan_start, loan_end)

the fraction of a year a construction loan is outstanding, to prorate an annual interest rate.

Function

Parts

Example

Why

NPV(rate, value1, …)

rate is the discount rate per period; value1, … are the cash flows, one per period, starting one period from now.

=NPV(0.07/12, C5:C40) + C4

the present value of a development's future cash flows, discounted at a hurdle rate, feeding straight into a residual land value.

IRR(values, [guess])

values is a range of cash flows, one per period, negative for outflows and positive for inflows; guess is an optional starting estimate for the rate. IRR solves for the rate iteratively, so leave guess out unless the function errors.

=IRR(C4:C40)

the return a development is expected to generate, the single number most feasibility studies lead with.

XNPV(rate, values, dates) / XIRR(values, dates, [guess])

as NPV/IRR, plus a matching dates range so cash flows land on their actual dates rather than even periods.

=XIRR(C4:C40, A4:A40)

a return calculation for a draw schedule whose payments do not land on tidy month boundaries.

PMT(rate, nper, pv, [fv], [type])

rate is the interest rate per period; nper is the total number of periods; pv is the loan amount now; fv is the balance left at the end, optional, 0 if left out; type is 0 if payments are due at the end of each period (the default) or 1 if at the start.

=PMT(0.065/12, 360, -B12)

the monthly repayment on a construction or acquisition loan.

IPMT(rate, per, nper, pv, [fv], [type]) / PPMT(rate, per, nper, pv, [fv], [type])

same as PMT, plus per, which period's payment to break down.

=IPMT(0.065/12, 12, 360, -B12)

split a loan payment into interest and principal for a single month, to build an amortisation schedule.

FV(rate, nper, pmt, [pv], [type]) / PV(rate, nper, pmt, [fv], [type])

as PMT, solving for the value at the end (FV) or now (PV) instead of the payment.

=FV(0.05/12, 60, -2000)

what a savings or reserve fund contribution grows to by a target date.

NPER(rate, pmt, pv, [fv], [type])

as PMT, solving for the number of periods instead.

=NPER(0.065/12, -1500, 250000)

how many months it takes to pay off a loan at a given repayment amount.

RATE(nper, pmt, pv, [fv], [type], [guess])

as PMT, solving for the rate instead; guess is an optional starting estimate, since RATE also solves iteratively.

=RATE(360, -1073.64, 200000)

back out the interest rate a loan actually carries from its known payment and balance.

MIRR(values, finance_rate, reinvest_rate)

values as in IRR; finance_rate is the rate paid on borrowed outflows; reinvest_rate is the rate earned reinvesting inflows.

=MIRR(C4:C40, 0.06, 0.04)

a return figure that does not assume every cash inflow gets reinvested at the project's own high IRR, a common criticism of a plain IRR on a development deal.