Warning!!!

P

Paul

I wanted to make a general comment about Excel for those of you that use the
spreadsheet to create journal entries for posting into your financial
systems. With all of the concern and new regulations making sure that the
financial statements of companies are correct, it seems relevant.

I use Excel to summarize daily reporting into a monthly journal entry,
actually several of them. I have found cases where the formulas in the cells
do not calculate the correct results, which would cause the journal entry to
be incorrect. The formulas range from simple "If" statements to "DSUM" and
"SUMIF" formulas. I have not been able to find a solution to the problem
within my spreadsheets. The only explanation I can come up with is that
Excel is not working correctly.

I take pride in my work and eliminate as much "human error" as I can. But
when I am using a spreadsheet that cannot produce accurate results, it
becomes a reflection on me, not on the software I am using. I don't think
there are many bosses out there that will accept the fact that software with
the Microsoft name on it will not work correctly.

If this problem is actually within Excel itself, then it seems to me that
Microsoft had better get its act together and clean up this software--and
quick. I wonder how Microsoft would like to have a company sue because they
produced inaccurate financials due to the fact that this spreadsheet software
cannot function as it is supposed to.
 
N

Niek Otten

<I have found cases where the formulas in the cells do not calculate the
correct results>

That is rather vague. Can you give examples?
Please give the cell addresses, the contents, the formulas, their location,
the expected result and what you got instead, the settings in
Tools>Options>Calculation and any User Defined Functions.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
A

Andy Wiggins

Examples?

Excel is not 100% perfect, but without specific examples I doubt if anyone
(especially your boss) will take this "Warning" seriously.
 
N

Niek Otten

<I have found cases where the formulas in the cells do not calculate the
correct results>

That is rather vague. Can you give examples?
Please give the cell addresses, the contents, the formulas, their location,
the expected result and what you got instead, the settings in
Tools>Options>Calculation and any User Defined Functions.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel
 
H

Harlan Grove

Paul wrote...
....
I use Excel to summarize daily reporting into a monthly journal entry,
actually several of them. I have found cases where the formulas in the cells
do not calculate the correct results, which would cause the journal entry to
be incorrect. The formulas range from simple "If" statements to "DSUM" and
"SUMIF" formulas. I have not been able to find a solution to the problem
within my spreadsheets. The only explanation I can come up with is that
Excel is not working correctly.
Click to expand...
....

Despite what you may believe, the odds are that any incorrect results
in your spreadsheets are the result of your own errors rather than
errors in Excel's internal functionality. There are exceptions, e.g.,
many of Excel's stats functions produce poor results for extreme (and
sometimes not so extreme) values, but for the most part Excel produces
the results it should given the restrictions on numeric precision.

If you're having problems with IF, SUMIF and DSUM, the problem is
likely either due to boolean comparison semantics you've misunderstood
or you have formulas that haven't been but need to be entered as array
formulas.

Without details it's impossible to diagnose your problem formulas.
 
G

Gary L Brown

Paul,
It sound like you need to ALWAYS wrap the Round() function around all your
DSums and IF statements.
HTH,
 
Top