Adding Window Functions to a Spreadsheet

Running totals, cumulative balances, and nearest matches: formulas between rows that remain correct.

Here is a equation many of us wrote first on. Put transactions in a traditional spreadsheet, akin Excel or Google Sheets, one per row, and keep a operating balance:

Copy the equation down, and it works. Each balance is the balance complete it affirmative the amount on its own row.

Now you discover a receipt from January 11 and insert a row for it, between rows 3 and 4. The new row has no formula, so you category one in, or copy it from above. But the balance below it now says =C3+B5. When the rows shifted, the citation was adjusted to keep pointing at the identical cell, and that division is no longer the row above. The new transaction is skipped without showing an error of any kind. The balance is fair incorrect from that row on.

Delete a row instead, and the equation below it becomes #REF!, and so does all balance following it.

None of this is a bug. To a traditional spreadsheet, “the former transaction” method “the division above”. The two concur lone as lengthy as nobody rearranges anything.

Surely there’s a improved way?

There is, if you cognize the tricks. Vertex42 suggests OFFSET(), or a related named range, to item at “the division above” in a way that survives edits. Exceljet suggests a sole SCAN() equation for the entire pillar in Excel 365.

What concerning the spreadsheet-like tools alongside repository foundations? In Airtable and Baserow, a equation can lone see the document it’s in, and the identical goes for NocoDB and Notion databases. People asking on their forums how to do a operating total in Airtable or Baserow get pointed to scripts, or to linking all document to the former one by hand.

So these tools evade the issue by not having a “row above” at all. But the need is there: a operating balance, the days since a patient’s final visit, the inventory remaining following all shipment.

SQL has an answer: opening functions. There’s LAG and LEAD for the former and next row, or SUM(amount) OVER (PARTITION BY document ORDER BY date) for a operating total. They additionally work, but they’re an advanced tool for developers penning queries, not for group operating in a table.

The actual improved way: PREVIOUS()

What we really average by “the former row” is “the former transaction, by date”. So let’s say exactly that. In Grist, a operating balance, or any cumulative total, looks akin this:

prev_balance = PREVIOUS(rec, order_by="Date").Running_Balance or 0
prev_balance + $Amount

PREVIOUS(rec, order_by="Date") sorts the array by ‘Date’, finds the current document (rec), and returns the former record. It’s a entire document (not fair a cell), so .Running_Balance gets that record’s balance. The extremely archetypal transaction has no former one, so its balance comes out empty, and or 0 takes attention of that.

The ordering is in the formula, not in the view. Insert a transaction anywhere, and it falls into location by its date. Delete one, and the balances near up about it. Sort the array by ‘Amount’ or display it, and all document motionless holds its balance by date, since that’s what we asked for.

Now, a credit cardstock document may have multiple cardholders, and all power desire their own balance. You power be tempted to divided the data into a array per person. Please don’t do that. Keep one table, and be specific:

prev_balance = PREVIOUS(rec, group_by="Card_Member", order_by="Date").Balance or 0
prev_balance + $Amount

PREVIOUS() now finds the former transaction by date among those of the identical cardstock member.

And since the command lives in the equation (order_by), nothing stops you from having multiple operating totals flank by side: one for the entire account, one per cardstock member, one that starts complete all month. Each pillar keeps its own count. Add a perspective connected to cardstock members and sorted by date, and you can choice a cardstock associate and see their transactions, alongside their balance adding up row by row.

PREVIOUS() has two siblings. NEXT() looks the another way, and RANK() gives all document its stance in the order, so RANK(rec, order_by="-Amount") <= 10 marks the ten largest transactions. The help article covers all three in additional detail.

Nearest match, latest record, and productive rate

Once a lookup knows concerning order, “previous” is fair one item you can ask of it.

Say people’s pay rates alter complete time, and you keep the history: Bertha earns $110 an hr starting August 30, 2019, and $125 starting March 24, 2020. A payroll admission from February 2020 needs the charge in consequence on its date, which is the latest charge that started on or before that date:

rates = Rates.lookupRecords(Person=$Person, order_by="Rate_Start")
rates.find.le($Date).Hourly_Rate

lookupRecords() gets Bertha’s rates, sorted by commencement date. find.le() finds the final one that’s small than or equal to (that’s the le) the payroll date.

Nothing concerning this is particular to dates. Commission tiers activity the identical way: market $5,000 and acquire 7%, market $10,000 and acquire 10%, and so on.

rates = Commission_Rates.lookupRecords(order_by="Sales_Target")
rates.find.le($Sales_Amount).Commission_Rate

Excel and Google Sheets users cognize this as an approximate match: VLOOKUP() alongside TRUE as the final argument. Microsoft’s own docs say the archetypal pillar afterward needs to be sorted, and if it isn’t, “the come back value power be item you don’t expect.” No error, fair a distinct answer. In Grist, the command is part of the formula: it sorts the tiers itself, no matter how you choose to current them on the screen.

The identical two lines answer a entire family of mundane nearest-match (sometimes called “approximate match”) questions:

• the pay charge in consequence on a stated date
• the commission tier a revenue total falls into
• the shipping charge for a package’s weight
• the tax bracket for an income
• the toggle charge on the day of a purchase
• the quantity discount for an command size

And for the simplest inquiry of all (the archetypal or final document in a group) you don’t equal need find(). Here’s a customer’s most latest order:

Orders.lookupOne(Customer=$id, order_by="-Date")

The minus sign method descending, so the archetypal equivalent is the latest.

The need for speed

Grist formulas are Python, so you could continually compose all of this by hand: get the records, iteration complete them, choice the correct one. We should inspect what that costs.

A operating total done by scanning, SUM(r.Amount for r in Transactions.all if r.Date <= $Date), looks at the entire array for all record. With 1,000 transactions, that’s a myriad steps. With 20,000, it’s 400 million.

On a test array of 20,000 transactions, PREVIOUS() computed the entire pillar of operating balances in seconds. The scanning type was motionless operating following five minutes.

The ordered lookups don’t scan. They keep the records sorted, and discover what you ask for alongside a binary search, which is the accelerated way to do it.

All caught up

Remember whenever we added the receipt from January 11 and it bankrupt the operating total? In Grist, you can add it anywhere. It takes its location by date, and all balance following it recalculates. The new row’s balance is right, and so is all cardstock member’s. Nobody has to copy a equation down, inspect an neighboring row, or fix an error.

That’s what you get from formulas that say what they mean: they update on their own, and they’re correct all time.

---

For additional details, see the assistance part Running totals, PREVIOUS, and ordered lookups. For a walkthrough of these examples, observe the webinar Advanced formulas alongside ordered lookups.