Sumwise trees

  • Tagged with:
  • Data grouping,
  • Excel,
  • Structured columns,
  • Structured references,
  • Structured rows,
  • Sumwise Application,

One of the most powerful features that Sumwise introduces is the concept of row and column structure that can be referred to directly in formulas.

In Sumwise, rows and columns can be nested underneath other rows and columns. This enables you to collapse and expand various parts of your model, and focus in on the areas that you are interested in. The concept of structuring data is not new or unfamiliar (at least as far as rows are concerned), but Sumwise applies this concept to a spreadsheet and takes it to a whole new level (excuse the pun).

In Excel, you can do something visually similar using data groups, but this grouping feature in Excel is purely superficial—let’s take a look why.

The first thing “wrong” with this is the strange way the trees are planted (so to speak) upside down. It is not very comfortable having the collapse and expand icons at the bottom/end of the group, rather than at the top/beginning. But let’s just agree that this is superficial and we can get used to it.

The real problem is that there’s no enforced connection between the logic of your model (e.g., Hardware, Software, and Total Revenue make up the items under Revenue) and the tree structure that is displayed. It would be pretty easy to continue to display say Hardware as looking as though it is nested underneath Revenue, but not have it grouped with the other revenue items. At best this takes manual time and effort to set up and maintain. At worst it’s a trap just sitting there waiting for you to get the grouping wrong and make a mistake.

What is different with Sumwise is that this structure is not just visual, but is inherently part of the logic of the model. For starters, if an item (say Hardware) looks like it is nested underneath another item (say Revenue), then it is nested. There is only one moving part. Contrast this to Excel, where you have to manage the nesting (indentation) and the grouping (pseudo structure). But, more than this, Sumwise’s allows you to refer to the structure you have created.

For example, in Excel, if you want to write a formula in the Total Revenue row to add up all the items underneath Revenue, you would write a formula like =SUM(B4:B5). Similarly, the formula in the Total Expenses row might be something like =SUM(B8:B13). These two formulas are essentially doing the same thing—adding up the other rows at the same level of the hierarchy. In Excel though, these formulas look different, and behave in completely different ways (i.e. the former adds up the two rows above it, whereas the latter adds up the 6 rows above it).

In Sumwise you can reference cells based on their structural positions.

So, the Total Revenue row can reference the other rows underneath Revenue using the keyword SIBLINGS. That is, from the perspective of the Total Revenue row, its siblings are the other rows in the same part of the row tree as itself.

In this formula, we have used the prefix R which stands for Row (i.e., we are looking for Siblings in the row structure, not the column structure).

Likewise, the cells in the quarters columns can add up the relevant months using the keyword CHILDREN, that is, from the perspective of Q1 its column children are Jan, Feb, and Mar, and Q2′s column children are Apr, May, and Jun.

(The formula uses the prefix C to indicate that we’re referencing the children in the columns, rather than the rows.)

Consider the benefits of this approach:

  1. These formulas are doing the same thing, and look the same. That is, the formulas in the Total Revenue row and Total Expenses row all look the same.
  2. When you add a new revenue row into the row structure, the formula in the Total Revenue row will automatically include this new row because of its position in the structure.

In building Sumwise models with these structured references, we have found that we are able to build complex models in a very simple way. Just consider all the times you simply want to add up the items above you—in Excel every formula needs to be hand-rolled and cared for. With Sumwise though, the formula =SUM(R[Siblings]) works in all cases, and never needs revising. We think that is pretty cool!

2 Comments so far Join the Discussion

  1. Doug

    19th November 2010 at about 8am

    There is a reason for the total to appear at the bottom… that is the way numbers are *usually* listed in a spreadsheet. The parent member of a group thus ‘belongs’ under its children, so an upside down tree is more appropriate than an upright one when making a typical template. This ‘post-order traversal’ applies to the data, while a ‘pre-order traversal’ applies to the hierarchy of members. Forcing the user to use the pre-order traversal view for data is not appropriate even when it should be an option (as it is in Excel, an option).

  2. Darren

    19th November 2010 at about 10am

    Doug, I agree with you that it is usual for the totals to appear at the bottom of a tree (i.e. the last node). Sumwise does not prevent you from doing this, it just implements the hierarchy in the more “normal” way – the images in the post show this situation.

Join the discussion


Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

videntes en linea gratis