EXAMPLE INCISIVE.ONLINE SPREADSHEET...

Incisive.Online Spreadsheet Analysis Example: Ep_aq.xls

Here is an example of the Incisive.Online spreadsheet analysis service results for a spreadsheet. In this example, we will use a spreadsheet that is available at the Minerals Management Service (MMS) website. MMS is a bureau of the Department of the Interior. Air Quality Emissions Reporting Spreadsheet for EPs and DOCDs page. On this page, there is a spreadsheet (xls) available for download here. A copy of the spreadsheet that we analyzed is available at: Ep_aq.xls

Analysis Results

In order to analyze this spreadsheet, it was submitted at: Incisive.Online spreadsheet analisys page. The following five reports were produced.

Review and Analysis of Results

The Risk Assessment Report has six main sections:

  • The Workbook Statistics section shows 1,074 formulas, but only 101 unique. This shows that the spreadsheet would be easier to review with a tool like Incisive Validate, which facilitates the process of reviewing formulas by grouping like formulas together. So, instead of reviewing 1,074 formulas, a reviewer could verify only 101 formulas and be done. With 8 sheets, this is a fairly small spreadsheet, and one that does not utilize names. It also shows no referenced external workbooks.
  • The Sheet Statistics section shows the names of the sheets along with thier protected and hidden statuses as well as their formula counts. No surprises here.
  • The External Workbooks section shows no externally referenced workbooks.
  • The Excel Function Use section shows the counts of how many times an Excel function was used, and whether that use was in a non-array or array formula. Glancing at this list shows extensive use of IF as well as some SUM use. The spreadsheet would benefit from the branch tracing available in Validate as well as in the structure view of Xcellerator.
  • The Named Ranges section shows no surprises. Although this workbook does not utilize named ranges, sometimes workbooks will have names will have #REF! or external workbook references in them.
  • Finally, the Unique Formulas section shows every unique (R1C1) formula in the workbook. Reviewing these formulas can provide insight into the structure of the spreadsheet. For example, the first seven formulas listed each have 60 occurrences. These are probably in neighboring columns. With a tool like Incisive Validate, you could review each of those formulas once, and approve all 60 uses. This is a huge time saver in reviewing workbooks. Also of interest is the last 45 (nearly half!) entries in this section. There are many formulas that are only used once. Although these are often legitimate formulas, they are sometimes errors that have been created during updates to the spreadsheet. In both Incisive Validate and Xcellerator, there are navigation tools that help you find these unique formulas that only occur once.

The Risk Summary Report has two main sections:

  • The Workbook Statistics section shows 1,074 formulas, but only 101 unique. This shows that the spreadsheet would be easier to review with a tool like Incisive Validate, which facilitates the process of reviewing formulas by grouping like formulas together. So, instead of reviewing 1,074 formulas, a reviewer could verify only 101 formulas and be done. With 8 sheets, this is a fairly small spreadsheet, and one that does not utilize names. It also shows zero referenced workbooks.
  • The Xcellerator Scan Results shows the number of results that each test found in a scan of Xcellerator. Xcellerator is a desktop tool that is available for purchase in the Incisive Store. Each submitted spreadsheet is scanned with an online version of Xcellerator running most of the available tests. The tests run are listed in this section with the tests with zero results listed last. In this example, I would begin by investigating the first test's results - the formula error test results. There are 75 results for the "Inconsistent Formula" test. After investigating and correcting these errors, the rest of the results can be reviewed as well. I would pay special attention to the 846 instances of constants embedded in formulas. These can be confusing and hard to maintain For locations of some of these test results, see the Critical Error Report.

The Critical Error Report, Heat Map, and Structure Map

  • The Critical Error Report shows the location of some of the results identified in the Xcellerator Scan and summarized in the Risk Summary Report. In this case, it points to some cells of immediate concern: Inconsistent formula results at 'EMISSIONS'!H11 and 'EMISSIONS'!I11. Investigation into these cells with Xcellerator shows that all of row 11 may have errors. inconsistent formula error example
  • Even without Xcellerator these results can be further investigated using the Heat Map provided by the analysis service. This file has color coded results in a copy of your spreadsheet. When this analysis is done with Xcellerator, the test results can be filtered an navigated to using the built in navigation tools of Xcellerator. You can review your spreadsheet's errors by reading the comments inserted into each cell with a result. In addition to this, the Structure Map provided by the online analysis service can be used to view the structure of the spreadsheet. Boxes are drawn when possible around formulas that are the same (have the same R1C1).