Strange Excel total error

[kgdickens]

Help! I'm having th strangest problem occur in the attached spreadsheet
It is truly a simple spreadsheet with no advanced formulas. The total i
K23 is incorrect (should be 1,545.36) yet, I can't figure out what i
wrong and Excel detects nothing. I manually added the M2-M18 for th
correct results. Even with rounding, it makes no sense! Thanks! i
advance for all assistance

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63886

 

[JE McGimpsey]

For various reasons I won't open unsolicited files, but there are a
couple of things you should check:

Because you mention 2 decimal points, I suspect the problem may be
rounding, see:

http://www.mcgimpsey.com/excel/pennyoff.html

You should also check that none of your values are Text, which SUM
ignores.

 

[Alan]

Help! I'm having th strangest problem occur in the attached
spreadsheet. It is truly a simple spreadsheet with no advanced
formulas. The total in K23 is incorrect (should be 1,545.36) yet, I
can't figure out what is wrong and Excel detects nothing. I manually
added the M2-M18 for the correct results. Even with rounding, it
makes no sense! Thanks! in
advance for all assistance!

Attachment filename: test.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=638864 ---
Message posted

Correct answer is 1,545.33375 according to my check.

Perhaps you need to buy a new calculator ;-)

Alan.

 

[Biff]

Hi Keith!

That's what rounding can do to you! Yes, it does make
sense. When you round, the value you see may not be the
actual underlying value. I noticed that you have the some
cells formatted as NUMBER>CURRENCY set to 2 decimal
places. That's where your rounding differences are.

For example, this formula you have: =41.25*3.5 -> =
144.375 and then it gets rounded to 144.38. The actual
value of the cell is 144.375 and that's the value that
gets used in any other downstream calculations.

A couple of things you can do is not round until the last
level of calculations or maybe use the TRUNC function:
=TRUNC(41.25*3.5,2)= 144.37 actual value.

Biff

 

[kgdickens]

Well! Alan,
It still doesn't explain a three cents difference!?
Maybe using Excel as the calculator is the problem! I did enjoy th
sarcasm

 

[JE McGimpsey]

If your problem is a display vs. stored value error, e.g.,

A1: = 1.13/2

which stores 0.565, but displays as 0.57 with 2 digits, it only takes 6
of these to accumulate a 0.03 apparent error.

 

[kgdickens]

Thanks! I fully understand, now! I guess when it comes to money, yo
have a decision to round up or down to balnce things out. With usin
=TRUNC, it comes out to $1,545.36! Darn those odd numbers not divisibl
by 2 !!!! Thanks, once again

 

[Alan]

I did enjoy the sarcasm!

No effense intended.

Apologies if any was taken.


You may wish to consider using 'Bankers Rounding' (lots of hits on
google) if you are running into issues with this.

Alan.