Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts

    Red face A sobering thought

    I see some very complex multi-spreadsheet operations being programmed here and wonder how many of these complicated systems have the occasional audit?

    http://www.bbc.co.uk/news/magazine-22223190

    For myself I am very careful with financial information and always install lots of cross-checks. To my continual surprise, the cross-checks regularly point out my errors and I am grateful to be able to sort these out at the time, rather than by a difficult archaeological exercise some time later (or worse, never).

    The way I do it is this, in summary. I find two ways to perform or check a calculation (usually a "big" result rather than every detail), or find an external verifier, and if there's a discrepancy it gets noted on a worksheet dedicated to such checks. If there are any discrepancies, that worksheet's tab gets coloured yellow (using a short bit of code in the worksheet event) so that wherever I am working in the workbook I immediately see that something has gone wrong - and can usually correct it straight away.

    Its a sort of real-time auditing.

    The detail is that if two sources are the same, the relevant checksum is zero, if they are not the same the checksum is the difference between them. Then I simply add up all the checksums and if the total is still zero I am OK, if not then the size of the reported difference usually points me right at the issue. I apply a universal tolerance at the checksum level otherwise small rounding and calculation errors eventually indicate false errors !

    I hope that this is helpful to those who are engaged in constructing large, particularly financial, spreadsheets where - in my repeated experience - errors can go unnoticed forever. Companies fall into this trap all the time, and it seems that Harvard professors aren't exempt either

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    Here's one of my favorites:
    =IF(ROUND(N2-N3,0)-ROUND(SUM(B4:M4),0)=0,"OK","ERROR")
    Cross Check.JPG

    Conditional Check.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Exactly so, RG !

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Martin

    If you add your checksums you could 'accidentally' get a zer0 balance and think everything is allright.
    For example, if two checks gave equal but opposite values (like +1,000,000 and -1,000,000)
    Worse, you could have a whole load of checksum errors, which just happen to add to 'near zero'.

    zeddy

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Zeddy you are right, and that's the penalty for a quick explanation.

    If any of the checksums ain't zero, the tab is coloured yellow.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    How did you get it to error N3
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Take a look at the simple example attached - post back if it needs explanation.

    The VBA is in the ThisWorkbook module.

    I would use more descriptive range names for the real thing.
    Attached Files Attached Files
    Last edited by MartinM; 2013-04-20 at 18:06.

  8. The Following User Says Thank You to MartinM For This Useful Post:

    XPDiHard (2013-04-20)

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by XPDiHard View Post
    How did you get it to error N3
    I changed the Sum() formula to leave out column M. Compare totals column for Liabilities. A common mistake if not copying formulas, like I just didn't drag it far enough. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    XPDiHard (2013-04-20)

  11. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    RG: add "psychic" to your powers - then read this !

    http://www.bbc.co.uk/news/magazine-22213219

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Martin,

    Nothing psychic about it just years and years of mistakes... I mean experience.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    "Good judgment comes from experience. Experience comes from, well, poor judgement"
    I believe that's a quote from Winnie the Pooh.

    But I still reckon you are psychic.

    zeddy

  14. #12
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    http://www.bbc.co.uk/news/magazine-19254835

    There'll be no point in doing that if the grinning politician is just playing to a gallery of servers and their coolers.

    What will happen to the heart-warming Hollywood film scene where the little girl who really, really believes in miracles delivers a speech which melts even the stoniest of hearts and the traders agree not to invest in the firm trying to turn her Daddy's farm into a rare earths mine?

    Her beautiful words would be studiously ignored by the Trade-a-Tron3000.

    The point in this matter is;
    words would be studiously ignored by the Trade-a-Tron3000.

    If a soul-less machine with advance Excel capabilities calculates "do this = profit" etc, and it means
    getting rid of the farm, or a forest, in theory it would have to arrive to a circular reference.
    because the machine will "know" in it's enshrined logic, it needs humans to service their own needs.
    If doing away with the farm, the human will not eat, or doing away with the forests, the human will not breath, and if the human will not breath or eat, human will eventually perish?
    If human perishes, eventually the "Sum of XYZ" will be zero profit.
    Correct ?
    So the calculation would have to be somewhere around earth's population and Max-Min Mass of things that allow humans to live, over Max-Min farms and trees to destroy for rare earth profits.

    It's seems like a inevitable catch 22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •