Exhibition Workbooks in ACBA Electronic Working Papers
Deleting Duplicates, Analyzing Lists, Arithmetic Combinations, Management and Control of VBA Coded Projects, Using Templates within the ACBA-EWP software, Using Named Ranges in Excel, List Formulae in Workbook, Analyzing Array Formulae, Working with "Controlled Statements", Noughts and Crosses, Sudoku - EWP in practice, List Unique Formulae - a discussion, Like a database, Audit Trails in EWP
The Electronic Working Papers (EWP) software, although originally constructed for auditors, is multi-functional. It is particularly suited to the analysis of and commentary on technical problems from an educational perspective. One of the first workbooks we published was our approach to solving Su Doku puzzles. This page contains descriptions of and links to a series of workbooks built in the EWP.
This sounds easy, but in this case the request came from an organization who only wanted two to three fields from their original database extracted. The exclusion of duplicates was secondary. An additional requirement was that the original database should remain unchanged. The workbook Deleting Duplicates contains both an analysis of the problem and the code appropriate to a possible solution. NB - Contains VBA Code.
This particular workbook analyses lists (paragraphs of free text) for the names of British birds that they contain. It then looks for any numeric content associated with the birds found. The workbook Birds - Analysis of WebLogs demonstrates and evaluates the potential for employing ACBA Tools functionality for analyzing lists.
This project stems from a request submitted to one of the myriad of Excel on-line discussion groups. The workbook Arithmetic Combinations outlines two potential solutions to the questions "How many combinations of a named group of variables are there?" and "What is the value associated with each combination?". The project deconstructs the primary elements of the process for creating combinations and considers how these might be applied to a solution wiich uses formulae only (through Excel's user interface) and one which employs VBA Code. This workbook also considers that rather curious relationship between Arithmetic Combinations, binary numbering and the maximum number of rows in a worksheet (Excel 2003 or earlier).NB - Contains VBA Code
Management and Control of VBA Coded Projects
Over the years, the development of code published on our Oddments page has grown and become difficult to monitor and control. The workbook created and prepared at Oddments Development Record is a first attempt to draw together and evaluate the separate developments and activities that are publicized under this generic heading.
This particular file demonstrates the use of both ACBA-EWP style templates (to generate fairly complex worksheet presentations) and the Documentary Evidence Index. The evidence index, which links directly to separate code snippets and associated form layout pictures, shows the development of code without including coded modules within the project file itself. This approach for presenting VB code to the potential user overcomes the frequent barrier that commercial entities employ to stop their staff downloading inappropriate files from the internet.
Using Templates within the ACBA-EWP software
The employment of templates is fundamental to the functional flexibility of the ACBA-EWP software. Examples of downloadable templates are listed at our web page concerned with Audit Resources. This workbook ACBA-EWP_Templates.xls demonstrates how the templates might be employed in practice within the context of the company software.
Using Named Ranges within Excel
Using names or 'named ranges' within Excel causes considerable controversy. As a company ACBA (UK) is in favour of their use, although we limit the scope of usage to those elements that meet our specific functional needs. This demonstration workbook NamedRanges.xls shows how we employ the naming of ranges at worksheet level to carry data from individual worksheets to a separate summary sheet. It also provides some technical background on the approach used.
Listing the Formulae contained in a Workbook
The ACBA Tools software provides a range of methodologies for listing the formulae errors and numeric values etc. within a workbook. Recent work using the tools suggested that, in certain circumstances, they were too slow to be used effectively. After consultation we have prepared a revised approach to the listing of formulae. This is much quicker. The linked workbook CountingSpecialcells.xls outlines the code and the changes incorporated to make it quicker. Users are welcome to test the code and provide feedback to ACBA on their results. Where appropriate this data will be included in the project. NB - Contains VBA Code
Analyzing Array Formulae
As a company we have to date (June 2011) sought to avoid using ArrayFormulae. The ACBA Audit Tools set was not originally designed to cope with these sophisticated constructs. We found the prospect of a single formula being capable of delivering a range of valid answers difficult and unnerving. However, a 2011 project designed to consider the whole gamut of practical uses and structures to which spreadsheet workbooks could be put, brought us into direct contact with this approach. We determined to review our Audit Tools software so as to offer an explicit analysis of those formulae on a spreadsheet which appeared to comply with the characteristics of an ArrayFormula. The analysis is designed to show the formulae, their range(s) of operation (both in terms of cell addresses and relative locations) and an evaluation of their validity/results.
A down loadable ACBA project file ArrayFormulaAnalysis.xls demonstrates the results of this analytical approach. It also exposes the limitations of our source material for testing the methodology. Accordingly we invite readers to submit their own files for analysis of the array formulae contained within them. We will undertake to up date our project file with analytical details of those files submitted. Please send any Excel files containing ArrayFormulae to Stephen Allen
Working with ACBA-EWP "Controlled Statements"
In practice the only extended use of "Controlled Statements" has been inside the company. In January 2012, we decided to relaunch the concept by presenting the process to the Excel DevCon 2012 seminar held in London. The original intention was to include a working Excel file that demonstrated the use of the statements in situ, but there wasn't time for such an approach. Readers may be interested to see this demonstration project.
TicTacToe (or Noughts and Crosses)
This project was constructed to demonstrate the nature of control that a designer can achieve over input. It uses the game TicTacToe (or Noughts and Crosses in the UK) to show how we can design the operating environment of a worksheet to manage and control input parameters. It presents two versions of the game. The first version only uses controls available at Excel's user interface (e.g. cell validation and text based formulae). The second version uses Excel's VBA functionality to achieve very close control over the game play and the presentation of results.NB - Contains VBA Code.
Sudoku - Electronic Working Papers in practice
This exhibit is of an active working file or project - current version 04 December 2012. The central subject is about how to employ EWP to solve a Sudoku puzzle. It is functional rather than pretty. We review the model from several different perspectives and employ the EWP indexing functionality to keep track of our comments and analysis.
However, the main role of the project is to show how fundamentally eccentric this model is when compared with most models in Excel. The workbook contains a series of linked worksheets that demonstrate the solution to a Sudoku puzzle published in the The Guardian, 18 February 2012. We then deconstruct the model to show how it works. This deconstruction takes the form of listing all the formulae within each fundamental element of the model, opening out each of the templates (on which the model is based) to show the hidden elements and providing a commentary on how and why we chose those particular structures and methods.
Listing the Unique Formulae contained in a Workbook
The capacity to list each of the fundamental formula types within a worksheet or workbook seems to be a common thread in a wide variety of commercially available spreadsheet audit and analysis software. This project considers how the software developers might have achieved their goal.
The EWP file UniqueFormulae.xls reviews a simple methodology for substituting the cell references generated from listing all the formulae with a standard string 'MyRef'. It then lists all the unique string values for the formula constructs. The workbook contains its own internal commentary that discusses some of the technical issues associated with generating a useful list.
This methodology was first developed in response to a query in the 'Microsoft Excel and VBA Users Group (LinkedIn). Reposting in the EuSpRIG forum generated some vigorous criticism. In particular, I was reminded that mosts analysts in this area employ the 'R1C1' notation for cell references. Nevertheless I took the view that examining a problem from a slightly different technical perspective could generate new insights. In particular, I considered how a phased approach to analysis to illuminate different aspects of the results for interpretation.
- Phase 1 – list the formulae without manipulation. Analyse for identical values. Could these be Array Formulae?
- Phase 2 – apply ‘MyRef’ standardisation to unfixed formulae (i.e. those without $ prefixes). What is the ratio between fixed and unfixed formulae? What does this tell us about the approach to construction?
- Phase 3 – apply the delete dollar function to the formula list and then reanalyse with ‘MyRef’. What are the fundamental formulae employed within the worksheet?
It is clear that the analysis of formulae within a workbook and the myriad of other concepts and structures is not simple. Commercial analytical software makes real progress towards reviewing workbooks from a variety of approaches. However, there is nearly always room for other perspectives. I commend this approach to reviewing 'unique formulae' as a potential alternative.
Like a database, whilst retaining the primary spreadsheet characteristics
We know that lists of data in spreadsheets can be treated as though they are databases, especially when each row has a unique identity field. These are often referred to as flat file databases. Excel has a series of inbuilt functions for analyzing such lists. In this exhibit, however, we seek to depart from a database as a list of data. Rather I wanted to collate together a series of (simple) numeric analyses to form a database like picture. This EWP project is based on preparations for my daughter’s wedding. The plans and preparations are real but the associated monetary values are fictitious.
The planning covered three main areas the preparation of our garden (for the reception), improvements to the house and the wedding itself. Each of these main areas was subdivided into smaller components of the project, such that there were steps in the main hierarchy. At the finest level of detail the working sheet looked as below.
Only the columns in green, marked ‘Costs’ and ‘Spend’, are used to collect data. In general the preceding columns are used to identify the individual items of expenditure and undertake mini-analyzes. The boxes of values to the top right of the spreadsheet summarises the values noted in the ‘Costs’ and ‘Spend’ columns. Each of the cells with a yellow background has a worksheet level name such that each of these values can be collated in a summary sheet.
The summary sheet might appear as below
While this form of approach allows huge flexibility to users in the management and control of a project, this is not the only important advantage. Spreadsheets offer considerable facilities for both colourful presentation and straightforward diagrams. The redesigning of the layout of our garden caused considerable thought and angst. The diagram that helped us think through the possibilities is shown below.
Engaging in a project with so many different elements, leaves the workbook creator with real difficulties concerning how to guide users/readers around the workbook.ACBA-EWP solves this by providing an index or table of contentss.
You can download the whole workbook from Meredih's Wedding.
Audit Trails in EWP
The closest Excel 2003 gets to the generation of audit trails employs the precedent and dependent arrows for formula auditing. There is no concept of who did what and when. In late 2014, I looked at ACBA-EWP again and evaluated to what extent the software could generate an audit record. The first fully fledged attempt at the creation of such a record was published in version 6.005 of ACBA-EWP. The record itself is recorded behind the scenes, but I have made provision for a new Working Paper at 'A004' which prints out an activity log as shown below.
I have created two versions of this style of Audit Log - one in Excel 2003 and a second in Excel 2010. Therir respectiive downloadable files are Audit Trails (Excel 2003) and Audit Trails (Excel 2010).
Last updated 10 October 2016