• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error-prevention strategies when using Excel

PP3321

Active Member
Dear Chandoo Community,

I am sharing some of the strategies that I came up with
in order to prevent errors when working with Excel

I would be extremely grateful if you could share yours

1. Visibility of formula
I try to check every formula contained in the Excel before submitting using VBA below.

Code:
Sub DebugPrint_GetAllFormulas()

Dim c As Range

For Each c In ActiveSheet.UsedRange
If c.HasFormula Then
Debug.Print c.Formula
End If
Next c

End Sub
2. State of mind (reminding me to focus on accuracy)

I have this message popping up everytime I open Excel
Code:
Private Sub Workbook_Open()
MsgBox ("Fast is fine, but accuracy is everything.")
End Sub
 
Last edited:

PP3321

Active Member
3. Error Check Sheet in Template (book.xltx)

I also have error check sheet in Template File, so every new worksheet will contain this sheet in order to remind me to double-check things
upload_2018-4-9_7-36-0.png
 

NARAYANK991

Excel Ninja
Hi ,

This is a good topic , and worth discussing.

However , a good error strategy should start with the design of the workbook itself , since planning a workbook thoroughly ensures that changes are not too many and do not come up too often. Most often , it is changes which lead to formulae being tweaked , and inconsistencies becoming a possibility.

There are auditing software which may help ; see this one :

https://www.spreadsheetauditor.com/

See these articles for some tips :

https://www.aatcomment.org.uk/5-ways-to-reduce-spreadsheet-risk/

http://professor-excel.com/avoid-errors-in-excel-6-strategies-to-prevent-mistakes/

One way to eliminate inconsistent formulae is to use tables as much as possible ; not only will the formulae be consistent , they will also be readable.

Narayan
 

Chihiro

Excel Ninja
Typically I perform same analysis using multiple methods to test accuracy.

Pivot Table (DAX), SQL native query, Excel formula etc. If there's any discrepancy I'd perform check on each process.

At minimum, I always use two separate process to compare results. With automated reports, I build in some indicator that flags me when something is off.
 

GraH - Guido

Well-Known Member
Hi,
I would add a "human" factor as well. Depending on the situation I have used these tactics with success to prevent all kind of errors:
- Explain your workbook design/plan of design to a colleague who has at least your or a better understanding of Excel.
- Or collaborate with such a colleague. It is kind of an agile approach.
- Ask a "normal user", or some-one from the intended audience, to use your solution and ask feedback.
- Ask domain experts to express their gut feeling about some main/critical/sensitive figures. They often have good intuition about the reality.
 

Peter Bartholomew

Well-Known Member
@PP3321
I do not think that the issue of Excel errors can be adequately addressed simply from an Excel perspective. Why one makes errors and why, having made them, one is intrinsically blind to one's own errors are fundamental characteristics of human cognition. I have my own opinion as to the Excel strategies one might adopt to control such errors but they would not receive widespread support. What I suggest you do with the section taken from a series of articles I have written, is to scan it as a brief intro to the work by Panko (spreadsheet context) and Kahneman (economics, Nobel laureate papers) and then refer to those.
 

Attachments

Peter Bartholomew

Well-Known Member
Have you written any further articles continuing on this theme ?
@NARAYANK991 So far I have written a sequence of four articles but #2 is the only one to address the nature of errors. Besides the general background topics, the articles introduced ways of working that do not rely upon single-cell formulae and relative referencing. The intended audience are engineering analysts and it is assumed that in the normal course of their work they deal with huge data arrays day in, day out, so the benefits of the spreadsheet end-user computing paradigm are not so obvious.

Article 1. Dan Bricklin - VisiCalc - A1 notation - Tables - implicit intersection - array formula.
Article 2. Cause and taxonomy of errors - Demonstration: Use of array formula to extract sub-lists.
Article 3. Human perception as a constructed reality - attentional blindness - cognitive biases (why we fail to find errors) - example continued on extraction of sub-lists - repackaging to remove unwanted helper ranges through the use of named formulae.
Article 4. Stats on use of Excel for critical engineering decisions - complete example by demonstrating the use of INDEX to constrain aggregation operations - Demonstration: the formula behind the dynamic flags in a FIFA WC2018 template.

Unfortunately the published articles are too large to be posted to this site but if there is anything from the above 'a-la-carte menu' that you would like to see, I would be happy to provide it. Peter.
 

Peter Bartholomew

Well-Known Member
Narayan, I posted part of my article with the intention of drawing attention away from the usual diet to 'tips and tricks' that enable one to develop the same error-prone workbooks at an ever faster rate and to provide references to papers that look at error in a wider and more fundamental context.

One topic that might provide food for thought is:
Given that the class of error that is most likely to find its way through to production release without detection is error of omission that leads to the incorrect representation of the business domain, how should one ensure adequate attention is paid to that part of the solution process and how best to make the assumptions overt?

I would argue that the conventional approach to spreadsheet development is ideally suited to masking such errors by relying upon 'simple' unstructured solutions, expressed using application-domain-specific notation (as opposed to using terminology derived from the business domain).
Such opinions do not necessarily buy me any friends :( !
 

NARAYANK991

Excel Ninja
Hi ,

One part of the problem may be that the people who request Excel based solutions are neither domain experts nor senior management capable of taking decisions.

If it is the middle manager who either develops an Excel based solution on their own , or interacts with an Excel developer who is not also a domain expert , then errors of omission are a given.

Narayan
 

Peter Bartholomew

Well-Known Member
I think Excel solutions are often provided within a business model that does not fully distinguish the role of client and developer. Is the developer merely assisting the client with their spreadsheet task, in which case the deliverable is an in-progress workbook, or are they delivering a solution to satisfy a pre-defined set of requirements?

In the latter case, the solution may not even show that it is built using Excel as a platform and the client is therefore not expected to know anything about Excel; it simply appears as a custom-developed app.
 

PP3321

Active Member
@Peter Bartholomew @NARAYANK991

Wow wow... thank you for your posts!!!

I did not read all the articles but I agree with the idea that we must accept the fact that humans make errors.

If you are sick and need operation, would you go to one super doctor,
or team of 3 average doctors????

After I read the article, I think now I want to be operated by 3 average doctors. It has less chance of making error, but with the conditions that they have great team work and cost same...hmmm

In reality companies cannot afford to have a team of Excel developers. Usually we have one superuser in the department.

Another problem is that he sometimes has big ego and insecurities too. If excel is developed by a team, his status or position in the company maybe be threatened...
 

Peter Bartholomew

Well-Known Member
@PP3321
At least your surgeons will recognise the professional standards expected of them. I suspect that many spreadsheet users are in denial when it comes to recognising that they are actually programming and could benefit from greater professionalism.

I have included an extract from the 3rd article of the series that I published. This looks at why one fails to find one's errors. Again, just skim my writing but take a look at some of the links. In particular, take a look at the young ladies practicing basketball and see just how easy it is to get the task right.
 

Attachments

PP3321

Active Member
@Peter Bartholomew,
Thank you for sharing those. I understand your point. We humans have tendency to over-estimate our own abilities.

I have a friend who used to work in Toyota and he always used to say 'work in a team. Team-work is everything."

Some years ago, I also saw documentary of a famous movie director who said "I do not trust myself. I only trust others."

I used to think this friend was crazy because why you create un-necessary duplication of roles, which in my experience lead to conflicts. I used to think we needed boundaries in a team. For example, If I am a Excel guy, I know Excel better, so why you are nosy about my work?

But maybe this way of management has limitations. We all need everyone to strip of their egos, and respect all regardless, and work as a team...hmmm
 

GraH - Guido

Well-Known Member
I missed out of some fun here... Thanks to all for sharing some great stuff.
One (two) more food for thought...
To answer a question of a journalist, Edison replied it only took him 1 try to make the light bowl. He never failed. All efforts (mistakes, errors...) were part of the (learning) process to get there...
I learned from a great, inspiring female trainer that focus on "things we did good", benefits in fewer errors the next time around.
Peter, you might be well on your way to make some "friend" here... And
I thought a couple of years ago I know about 80% of Excel for sure. I know a lot more since then: perhaps I master something like 5%. Ask me tomorrow, I'll know even more. Less then 5% for sure.
It is quote I used several times last year, when I had the pleasure to organize some Excel awareness and training sessions in the company I work for.
 

PP3321

Active Member
Try asking Excel users to rate their level of competence!
I suspect the more you know, the more you are aware of the shortcomings in your knowledge. Mine is distinctly patchy.
Yes but you cannot under-estimate either. I think the challenge is to communicate and let people understand all the weaknesses while not also killing motivation and offending them.

I think we just need every stakeholder to understand regardless of expertise in Excel there are human limitations to look after (which we should not take personally as we are all humans), so we need a special organization set-up
 

chirayu

Well-Known Member
Read the thread and looks like it took a massive turn from error checking to state of mind lol.

In any case I'm just going to talk about error checking

1) Use formulas e.g. ISERROR/ISNA/IFERROR/COUNTIF etc. to check for errors and duplicates
2) Use conditional formats to check for errors and duplicates
3) Filter the raw data as per formula/pivot & see if data matches for a few records against the formula/pivot
4) Minimize manual input through automation e.g. Excel Templates/ VBA
5) Data extract in preset format from whatever platform you use

That's pretty much all the stuff I do
 

PP3321

Active Member
@chirayu
Thank you for your post! The way I see it is not just mind-set. It is challenging this assumption that we can prevent errors. Tricks and techniques are good, but we also need to accept limitations of those and embrace alternative solutions such as teamwork & initial planning

Can you give example of No. 5?
5) Data extract in preset format from whatever platform you use
 
Top