Jump to content

Checking your figures: helping you discover errors


Recommended Posts

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. 

 

 

Link to comment
Share on other sites

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

 

Excel Qucik Calc.jpg

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...