On Tue, 22 Feb 2005 20:04:42 -0800, Mike H wrote:

> On Wed, 23 Feb 2005 11:48:45 +0800, Ellis Yu wrote:
>
>> I found there's a problem in the rounding in formula "sum". I've a
>> cell to sum all the below figures.
>>
>> $2,220,459.605
>> ($55,511.49)
>> ($55,511.49)
>> ($1,998,413.65)
>> ($37,182.70)
>> $4,842.80
>> ($121.07)
>> ($121.07)
>>
>> The result is $78,440.935 and then I decrease my decimal place to 2. It
>> becomes $78,440.93 But if I enter the figure directly instead of using
>> formula "sum" and do it the same way. It becomes $78,440.94. It's so strange
>> to me. Anyone know how to solve it? Please help

> Well, you've seen a little Excel precision problem. The amount you get
> by using the "sum" formula isn't exactly 78,440.935--it's a little less.
> Excel gives that sum as 78,440.934 999 999 600... So, that explains the
> rounding issue and brings up a new one. I personally wasn't aware of
> this behavior in Excel (but it's nothing new to computers in general
> that use floating point numbers).
>
> So, I'll post this little note for your edification and go off and do
> some research on floating point or fixed decimal precision in Excel when
> doing addition.

To answer my own post--Use help and do a search for "precision". You'll
find that Excel number precision is 15 digits. Someone with this stuff
fresher in their mind than me will have to jump in, but it looks like
you've asked it to accomodate a range a magnitude + fractional part
greater than that.

It's clumsy, I know, but you can see this behavior if you sum the
numbers that are closer in magnitude together as a group, and then add
the numbers that are outside that range. For example, sum all those
negative numbers as well as the small positive number, and then add that
number that's in the range of 2e6.
--
Mike H