Reporting on Data

Question

Reporting on Data

Steve Jones - SSC Editor

SSC Guru

Points: 734449
More actions
July 8, 2013 at 8:28 pm

#151258

Comments posted to this topic are about the item Reporting on Data

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125522 More actions · Answer 1

In addition to formulas not being replicated properly, another problem is when a cell that appears to contain a numeric value is treated as text by Excel, because it contains an embedded space or something. That's easy to overlook when working with a long series of numers. So when applying a @SUM formula across the column, or just highlighting a range of cells, the malformed "number" is excluded from the total.

10.5

12 .1

4.5

-------

15.0

Here is an actual example where the mistake ended up in a published economic report that was cited during the 2012 presidential race.

http://www.businessweek.com/articles/2013-04-18/economists-spreadsheet-error-upends-the-debt-debate

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 2

Hi Steve, you mentioned -

We need to learn more about statistics and analysis to ensure that as we work with business people to query data, we understand what information we are extracting out of data sets.

I agree, the problem is:

1. Users themselves don't always know the system/application well enough to ask for meaningful reports.

2. BPA's and Developers don't know how to analyze requirements documentation, or the developer relies on the BPA to acquire the info - unfortunately the BPA might not know the data or application well enough to do it right, or just accepts what the user asks for - without asking why they want it or what they plan on using it for.

Result: forever updating the same reports or creating variations of the same reports.

Most reports are process based (to assist users or managers to check if all is fine),

yet very few are analysis based (which assist directors to check if company is performing ok or facing closure if they maintain status quo).

phonetictalk Hall of Fame Points: 3582 More actions · Answer 3

I'm surprised (or I'd be surprised) that there isn't a way to automatically audit Excel sheets for these sorts of mistakes. I know Excel will highlight if a formula isn't consistent with the other formulas in the column or if a cell is formatted as text and others aren't. There has to be (or should be) a tool to centralize that check.

Another problem I could see in Excel (and reports in general): using a hard-coded value. E.g. 31 because that's how many sales areas there are. Works great. When 31 becomes 30, the report is close enough no-one notices. How long, and how wrong, does the report get before anyone notices?

Leonard
Madison, WI

Eric M Russell SSC Guru Points: 125522 More actions · Answer 4

phonetictalk (7/9/2013)
I'm surprised (or I'd be surprised) that there isn't a way to automatically audit Excel sheets for these sorts of mistakes. I know Excel will highlight if a formula isn't consistent with the other formulas in the column or if a cell is formatted as text and others aren't. There has to be (or should be) a tool to centralize that check.
Another problem I could see in Excel (and reports in general): using a hard-coded value. E.g. 31 because that's how many sales areas there are. Works great. When 31 becomes 30, the report is close enough no-one notices. How long, and how wrong, does the report get before anyone notices?

I'm using Excel 2010, and when I format a column as Numeric, then enter a series of floating point numbers with one having space between last whole digit and decimal, there is no visual cue. That malformed number will simply be excluded from the total.

Another problem comes when importing Excel sheets using SSIS. Any rows marked as hidden are excluded. This type of thing can happen when accounting or marketing department submits data in the form of Excel sheets, and they take the liberty of blinging it up with formatting, adding or renaming columns, etc.

PowerPivot, when the underlying data is stored in SQL Server or Analysis Services, and Excel used just as a presentation tool, mitigates these issues to some degree.

But when it comes to storage or exchange of data, Excel is terrible. Always insist that people providing file feeds do so as delimited or fixed width text.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho