1. ## 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. 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

3. Exactly so, RG !

4. 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. 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. How did you get it to error N3

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

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

XPDiHard (2013-04-20)

9. Originally Posted by XPDiHard
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

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

XPDiHard (2013-04-20)

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

12. Martin,

Nothing psychic about it just years and years of mistakes... I mean experience.

13. 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. 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
•