h a l f b a k e r ySugar and spice and unfettered insensibility.
add, search, annotate, link, view, overview, recent, by name, random
news, help, about, links, report a problem
browse anonymously,
or get an account
and write.
register,
|
|
|
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".
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] Are you suggesting some form of numerical spell-check? If so, I'm all ears! |
|
| |
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. |
|
| |
For example, it could correct two initial capital numbers. |
|
| |
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? |
|
| |
[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. |
|
| |
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." |
|
| |
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. |
|
| |
Good problem solving, problem solver. |
|
| |