Skip to main content

Posts

ACBA Mapping: Employing ‘UsedRange’ in Excel

When reviewing a worksheet the ACBA Mapping software uses VBA’s ‘UsedRange’ function to identify the full scope of the sheet. This function is excellent for identifying separate ranges employed in a worksheet, but has some drawbacks. One of these drawbacks has been identified while reviewing the Enron corpus of files ( https://figshare.com/articles/dataset/Enron_Spreadsheets_and_Emails/1221767 ) The Enron corpus of spreadsheets and other correspondence is huge. Felienne Hermans however has whittled the number of workbooks down to 16,189 unique items.   https://www.felienne.com/archives/3634   The workbook that clearly displayed the UsedRange issue is named ‘benjamin_rogers__938__historical outages.xlsx’. Clearly the files have been renamed to prevent duplicates. In this blog though I will refer to it as ‘Historical Outages’. In the original Mapping software, users were advised when the ‘UsedRange’ exceeded half a million cells. This allows the user to escape from the process and t
Post a Comment
Read more
Recent posts

Walberswick – June 2023

 Patrick and I chose the hottest week of the year so far to stay at Esme’s caravan in Walberswick. I spent the first few days on my own to check the workings of the caravan. It is old and showing signs of wear and tear, but the absence of curtains in large south facing window was a real irritation. Bryony saved the day by bringing our large picnic table cloth, which we were able to pin over the window for shade. That turned out to be vital given the weather. I claim that it was all the fault of COVID 19 that I am now over weight and unfit. I used these first few days to identify how unfit I was. The answer turned out to be “very”. The walking of the shingle beach from the camp site to the Blythe estuary was a nightmare. In previous years, I would get up early and walk for an hour or so before considering the activity for the day. This was based on a circular walk up the river path and over the heathland that surrounds Walberswick. Morning Walk This took rather longer than I had
Post a Comment
Read more

Oddities and Queries in MS Excel – Review001

  We know that problems frequently arise with spreadsheets. Usually these problems are referred to as errors and give spreadsheets a bad reputation. The aim of this series of reviews is to examine these potential problems in greater detail, using the ACBA Mapping software as the starting point for the investigation. This first review looks at a single file from the collection of excel files deriving from the collapse of the Enron Corporation . This particular file gave rise to a wide variety of interesting conundrums and offers a starting point for this series of reviews. The issues arising from this file are considered under the following headings. Interrupted column formulae Displaced formula Extended use of formulae   Irrational references Unusual column data sequence The “BR New York ticker 10_01” spreadsheet comprises 18 worksheets, 17 of which are visible and have been mapped. Several of the worksheets have almost identical structure. The review below uses examples f
Post a Comment
Read more

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 fol
Post a Comment
Read more

Various Uses of Maps

A preamble to using ACBA Maps The National Geographic magazine has an excellent article by Geoff McGee (12 October 2015) - How to Make Maps and Influence People . It provides a brief over view of the approaches uses to make their maps influential. Maps may or may not be beautiful, but they are always intended to make an impact, to focus the readers’ or viewers’ attention on something important. An extract from the McGee article states:- “Mode says the historical masters of persuasive maps, though, were the British during their imperial height, when maps helped promote the size, power, and presumable invincibility of a small island nation’s global empire. They used not just maps themselves, he says, but also visual and geographical tactics to shape the viewer’s perception.” Before focussing on ACBA Maps, it is worthwhile considering how maps have developed over the millennia. Maps have always been designed to influence their readers. The mechanisms employed to guide these crea
Post a Comment
Read more