Related Row Computations (e.g. rollups, counts, etc.)

  • Updated

In Spreadsheet.com, Related Rows enable you to link data between worksheets. These links and accompanying Related row lookups are invaluable for formulas as they allow us to reference a single source of truth. Also, using related rows (and lookups) in our formulas ensures that our formulas match the latest data, with references to worksheets that are updated as new data is added.

With related rows, you can perform computations on either end of the link. Your source of data can have formulas whose outputs you reference, or you can link to the original data and perform computations on that data in a new worksheet.

Using Functions on Related Rows and Related Row Lookups 

With related rows and related row lookups, you can perform computations on both the outputs of the lookup or lookup the output of a computation in your related row.

For instance, in the animation below, we develop a Bonus Calculation worksheet. First, we set up a worksheet that relates to our CRM, pulling in some of the deals that are closed or nearly closed, as well as important data like owner, revenue, and stage. Then, in our Bonus Calculation worksheet, we add in formulas to calculate the number of deals each salesperson has made, the sum value of those deals, and calculate a bonus for the salesperson as a percentage of their deals.

Computation_Related_Rows.gif

Using related rows and related row lookups here allows us to have a central CRM worksheet with the latest on each deal AND a separate worksheet created by the manager that the salespeople can see but not update that shares their bonuses using the latest data from the CRM worksheet.

Some Relevant Functions

Like the special functions for Row Hierarchies, you can use the following functions for unique computations on related rows and related row lookups:

And other Statistical Functions

When using functions like these, it can be a good idea to add them either into the original worksheet and lookup the output of these formulas. Or create a related row lookup, then apply these formulas to the lookup (as we do in the animation above).

For more on formulas check out our Formula articles or articles on advanced formatting with formulas.