1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'The Lounge' started by PP3321, Apr 9, 2018.

  1. PP3321

    PP3321 Active Member

    Messages:
    367
    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 (vb):
    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 (vb):
    Private Sub Workbook_Open()
    MsgBox ("Fast is fine, but accuracy is everything.")
    End Sub
    Last edited: Apr 9, 2018
  2. PP3321

    PP3321 Active Member

    Messages:
    367
    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
    ThrottleWorks likes this.
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
  4. PP3321

    PP3321 Active Member

    Messages:
    367
    @NARAYANK991
    Wow thank you for your reply and these articles!

    I realized that designing & planning are very important.
  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,826
    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.
  6. PP3321

    PP3321 Active Member

    Messages:
    367
    @Chihiro
    Thank you so much for your answer
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    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.
    ThrottleWorks and PP3321 like this.
  8. PP3321

    PP3321 Active Member

    Messages:
    367
  9. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    291
    @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.
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Have you written any further articles continuing on this theme ?

    Narayan
    ThrottleWorks likes this.
  11. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    291
    @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.
    ThrottleWorks and NARAYANK991 like this.
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Thanks. I am going through the paper by Panko , and will post back here with my questions if any.

    Narayan
    ThrottleWorks likes this.
  13. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    291
    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 likes this.
  14. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    291
    p.s.
    That criticism is not one I was leveling at this forum (just to be clear)
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    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
    ThrottleWorks likes this.
  16. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    291
    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.
  17. PP3321

    PP3321 Active Member

    Messages:
    367
    @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...
    ThrottleWorks and NARAYANK991 like this.
  18. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    291
    @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.
  19. PP3321

    PP3321 Active Member

    Messages:
    367
    @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
  20. PP3321

    PP3321 Active Member

    Messages:
    367
    Peter Bartholomew likes this.
  21. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    291
    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.
  22. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    751
    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
    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.
  23. PP3321

    PP3321 Active Member

    Messages:
    367
    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
    NARAYANK991 likes this.
  24. chirayu

    chirayu Well-Known Member

    Messages:
    873
    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
  25. PP3321

    PP3321 Active Member

    Messages:
    367
    @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
    Peter Bartholomew likes this.

Share This Page