estimating Checking your figures: helping you discover errors

[ToughButterCup]

Budgets and spreadsheets go together like [...........insert your own idiom here..............]

 

And so do errors: errors of fact and errors in formulae. Realising that, recently, every time it rains, I'm in the office trying hard to avoid checking our spreadsheet. 

 

Errors of fact

Not a great lot to be done here. My only suggestion is to get a mate to check with you. @MrsRA is better at this process than I am, but we still manage to avoid the issue because it's so hard (for that read time-consuming) to do something which 'should' be easy. It isn't.

 

Errors in Formulae

Is there anything that can help the hard, detailed, mind-numbing, eye-watering, annoying slog

Yes

Here is a simple search for all versions of the term 'formula auditing' (because there are so many versions of spreadsheets out there)

And here is one website's summary of several approaches to checking formulae in the most common (I think) spreadsheet of all.

Just sayin'. Guess what I'm doing this morning. 

 

 

[PeterW]

Mine runs to about 800 lines, and its detailed to items such as paslode nails..... I don't go as far as putting each hinge/screw on their though !

[SteamyTea]

In Excel you can highlight and get some quick answers down in the bottom right hand side.  You can customise the results by right clicking.

 

The real way to do it is to use double entry book keeping methods, but this is tedious to set up and involves more work.

 

Main thing I do is to keep it simple.  So I try and always use absolute referencing when I can (names ranges and the use of F4) and often use two different methods to get the same result i.e. Average and then Sum and divide.

 

I still make many mistakes and can spend days looking for them.  One of the classics is mixing up text and numerical values in a range.  Of a simple decimal point out of place

 

[SteamyTea]

If you look at my results you will see that the Max is showing 128, which it complete nonsense as water boils at 100.  So I know I have a mistake.