Dear LazyWeb: Excel Question

[mlerules]

So, a friend has posed this query which I'm now opening up to my LazyWeb as well:

I use Excel a lot at work, managing revenue coming in and going out. Occasionally, Excel will round numbers in such a way as to cause financial errors.

Here is the latest example: $1080.90 * .35 = 378.315 and 1080.90 - 378.315 = 702.585. No problem. HOWEVER, if these numbers are formatted as currency or accounting, Excel rounds them and the result is this: 1080.90 * .35 = 378.32 and 1080.90 - 37.32 = 702.59 which is incorrect at the bank. So, my question is how does one manage this rounding in Excel to prevent this sort of error?

Thanks for any help/tip/suggestions!

Comments

[stacymckenna.livejournal.com]

When using currency formatting, the 3 decimal places data is actually still there and being used in calculations, hence both numbers appearing to be rounded up to the next penny. Personally, I leave everything in decimals until I need something actually truncated/rounded, and then perhaps I'll use the Currency format.

Rounding can be highly specialized - I highly recommend reading instructions for ROUND, ROUNDUP, and ROUNDDOWN functions to determine which best suits your specific worksheet/data.

[mlerules.livejournal.com]

Thanks!