Subscribe Bookmark


Jun 13, 2014

More spreadsheet challenges -- Part 3

Last week, we looked at the first scenario in our example showing some guesswork that Excel performs when adding to existing data that are included in a formula. This week, we’ll look at Scenarios 1b and 2, which further show the behavior of Excel after rows are inserted within a formula range.

First, let's remind ourselves about the spreadsheet we are using in these examples:

And here’s what I’ve added to Scenario 1b. Note the space in cell C6.

In this scenario, the first adjacent cell, C6, was skipped, and values were entered in subsequent cells. Excel will not assume that the values should be added to the Sub and Grand Totals.

The major point here is that Excel cannot accurately determine a user’s intent because there is no overriding governance that applies to all cells in a column. Excel will  always add values placed in adjacent cells to a summed formula. This may or may not be the user's intent. If it's not the user's intent, the user will have an incorrect sum, in this case.

Scenario 2 (below) incorporates named ranges. In the spreadsheet below, cells D3, D4 and D5 have been assigned the named range "xum." The summation formula is =SUM(xum).

Here, cells D3 through D5 were added to a named range “xum.” The formula in cells D10 and D24 = SUM(xum). Because the formula was defined with a named range and not cells, Excel cannot assume that values added to adjacent cells should be added to the total. Therefore, any values, even those added to the first adjacent cell have no effect on the Sub and Grand Totals (see column D below).

Using Excel Analytics

Over time, Excel has added more analytics, but these require the data upon which they work to be formatted more like a data table and less like a freely formatted assemblage of cells. So, for Excel to become more analytics friendly, it has to take on a format that is beyond its original purpose. And Excel still lacks the type of insight and discovery capabilities that true statistical analysis software can offer.

Next week, we’ll look at on example of how JMP can add more value to Excel analysis.

Note: This blog post is part of a series on errors in spreadsheets. New posts in this series appear weekly on Wednesdays.