Half a croissant, on a plate, with a sign in front of it saying '50c'
h a l f b a k e r y
Why not imagine it in a way that works?

idea: add, search, annotate, link, view, overview, recent, by name, random

meta: news, help, about, links, report a problem

account: browse anonymously, or get an account and write.

user:
pass:
register,


                       

Spreadsheet Discrepancy Finder

Tries every combination of 2 numbers to find the error
  (+1)
(+1)
  [vote for,
against]

I'm an actuary [gezundheit! *thank you*], and every time I type up a huge spreadsheet and find a discrepancy between two numbers that should have been identical, I wish for the same product.

It's a discrepancy finder. I tell the computer "This cell is off by $153.47", and it searches every combination of 2 cells, adding, subtracting, dividing, etc., even within multiple spreadsheets in the same folder, in an attempt to find numbers that sum up to 153.47. That's what I usually have to do by hand.

After trying all the 2-number combinations, it starts on the 3's. These are very hard to find, even by hand. Yes, the program is slow and may take hours, but (a) it runs in the background, so I can look for the problem myself at the same time and stop the program if I find it myself; and (b), instead of searching for the error and not eating pizza, I can set the Find Discrepancy program to work during lunch while I eat the pizza.

Furthermore, the program isn't totally slow, because it uses positional logic. That is, if you have an error in the 4th cell which is the April column, the program first checks other cells which are fourth in position or in the same row or column as cells labeled "April".

phundug, Feb 20 2004

SpACE - Spreadsheet Auditing from Customs http://www.hmce.gov...opertyType=document
[jutta, Oct 05 2004]

European Spreadsheet Risks Interest Group http://www.eusprig.org/
Yep, these folks are serious. [bristolz, Oct 05 2004]

[link]






       Is the discrepency due to a typo from data entry?
yabba do yabba dabba, Feb 20 2004
  

       [yabba] Are you suggesting some form of numerical spell-check? If so, I'm all ears!
phundug, Feb 20 2004
  

       That would be very hard to do. How would it know which numbers were spelled correctly? You can certainly catch cells that are outside of a range of values pretty easily. I was just trying to visualize the problem.
yabba do yabba dabba, Feb 20 2004
  

       For example, it could correct two initial capital numbers.
phundug, Feb 20 2004
  

       1. Ha!
2. How many columns of data would you have to check?
3. Are all of the columns related in such a way that you would have to test every combination?
yabba do yabba dabba, Feb 20 2004
  

       [phundung] If you are talking about typos that, for example, end up in balances not being equal when they should then you might be using the spreadsheet in a less than optimal way.   

       All absolute numbers (e.g. unit cost, ratios, years depreciation, exchange rates etc) should be entered once and all places where a value must appear should refer to the appropriate master cells.   

       E.g., unit price of a desk is placed in a sheet with all other prices. All the occasions that the price of a desk is reverenced shall refer to the original value, not have that value typed in. You can then be certain that all cells are using the same value and it is quick to check that value is correct. An added benefit so when you update the values it ripples through automatically.
timbeau, Feb 21 2004
  

       Patrick O'Beirne, a systems consultant specializing on spreadsheets, writes in e-mail:
"Such a product exists, it's a spreadsheet auditing tool called SpACE. Of the many tests it does, one is to find up to six numbers that add up to a given amount."
jutta, Aug 30 2004
  

       Excel has an auditing feature. If this is not enough, it also supports VBA which you could use to write your own code to do whatever you want. If you can't or don't like to write code you can hire someone or get and intern to do it. The only thing is that you need some kind of logic or equasion for your code to know what is an mistake and what is not. Otherwise it's kind of impossible.
ProblemSolver, Aug 31 2004
  

       Good problem solving, problem solver.
yabba do yabba dabba, Sep 01 2004
  
      
[annotate]
  


 

back: main index

business  computer  culture  fashion  food  halfbakery  home  other  product  public  science  sport  vehicle