Skip to main content

ACBA Mapping – Spring 2022 Revision

Nearly all spreadsheet error values are as a result of a problem within a formula, but …

The ACBA Mapping software was made available in its ribbon-based format in December 2021. There had been considerable testing of the technical aspects of the software and its interaction with the ribbon. However, relatively little testing had been undertaken on publicly available spreadsheet collections like the Enron Corpus.

I began reviewing the Enron spreadsheets on a workbook by workbook basis in early spring 2022. The results of that review will be published later. However, it became clear the different authors had very different approaches to the construction of spreadsheets.

In particular, I came across cell constants which were themselves error values. I should emphasise that these were not as a result of formula errors. The original ACBA Mapping software had not taken account of this possibility.

Cell Constant - Error Values

The spreadsheet file “NYMEX Straddles” displays the following worksheet.


The cells containing error values are highlighted. These are not formula cells. In fact there are no formulae on this worksheet.

Under the December 2021 version of ACBA Mapping all the non-empty cells would have presented as ‘T’ with a background of red. Under this revision the error value cells display as ‘r’ – for error – and these have a dark green background.

The ltter 'r' represents a cellerror value.

Similarly, the workbook review of errors has been amended to include the error constants.


 Chart Sheets

For the most part Chart Sheets are visual representation of data from a worksheet. They are the end product or representation of a data analysis. While, we have now included Chart Sheets in the list of sheet like objects, the table will not show any further analyses.

Chart Sheets are included in the list of sheets.

Downloading ACBA Mapping

The ACBA-Mapping software is distributed as an Excel Add-In and can be downloaded from the link.
For those not familiar with handling Excel Add-Ins, instructions on what to expect and where to load your Add-Ins can be found at Generating the "ACBA-Mapping" Add-In.

Access to Help

There is a discussion group dedicated to providing a forum for help and information - Excel - Cell Mapping. I recommend you join the group.

Alternatively, you can email me directly from the Stephen Allen link within the form.

Acknowledgements

Experts in Excel have offered their advice and I am very grateful for this. My thanks go to Patrick O'Beirne (Systems Modelling), Jan Karel Pieterse (JKP Application Development Services) and Hans Hallebeek (HC & TS).

Spreadsheet Risks

Spreadsheet errors and inconsistencies is a subject that is of concern by professional developers as well as amateurs. The European Spreadsheet Risk Interest Group (EuSpRIG) considers the problems from an academic and professional perspective. The following links provide access to the EuSpRIG website and discussion forum.


Comments

Post a Comment

Popular posts from this blog

Cell Maps - Critical Comments

Cell maps are intended as tools for reviewing spreadsheets. If you spot an error or an inconsistency in the cell map this should be recorded and, if practicable, corrected. The cell mapping software provides a method for recording a reviewer's comments. All comments are linked to a specific map (or data table), The comments for a workbook under review are collated in a single worksheet. In this case, there's just one comment. The comment above refers to the area highlighted towards the bottom of the map below and are hyperlinked.to the specific area of the map through the column headed 'Map/Source Link'. The top left cell the source range provides a link back to the 'Comments. worksheet'. The yellow cells suggest that users should be entering information for collection by a formula, but which one.  At the this point I went back to the orginal worksheet and traced the dependents for some of these 'so called' user entry cells - also coloured yellow as it ...
Post a Comment
Read more

A Revised Cell Map!

  In the Blog ' What is a Cell Map? ', I included an image of a Cell Map. On reflection the presentation was muddled. A simpler and clearer design seemed worthwhile. The result below is more clearly rectangular, with the headings for the map, the formula table and the names/validation table matched along the same row. The validation table is new and highlights where user inputs are limited by validation and the nature of the validation criteria. The revised software is available at ACBA-Mapping . In addition, although software is intended to be intuitive and self explanatory, it rarely is. I have created a user group at    MSExcelCellMapping@groups.io   . You are welcome to join and ask questions about the software. https://drive.google.com/file/d/1w3tHk1l-G9LjmCsOqMBkJpE9kaSj0iX_/view?usp=sharing Spreadsheet errors and inconsistensies is a subject that is of concern by professional developers as well as amateurs. The European Spreadsheet Risk Interest Group (EuSpRIG...
Post a Comment
Read more

Loading an Excel Add-In

  Microsoft offers a range of Add-Ins and other extensions to Excel’s normal functionality. These are accessed most easily from the ‘Developer’ ribbon. The Developer Tab Clicking the Add-Ins icon, offers the user a list of all Add-Ins currently available to your Excel program. The problem comes when your particular program isn’t listed there. Even when you know where the Add-In file is and double click it, the functionality is still not activated. According to SuperUser.com , the problem results from security patch in KB31152, released in July 2016. Apparently, Microsoft software engineers have said: "With this update, we changed the behaviour of Excel so that it will not load certain file types (including .xlam) when they are untrusted. The easiest workaround is to find the add-in that is causing you trouble, right-clicking on it in Windows Explorer, and checking Unblock" However, SuperUser.com recommend placing the add-in in a Trusted Location (in Excel, go to File ...
Post a Comment
Read more