The sum of these numbers should be zero (0). However, no matter what the
cell format, the following numbers add up to 3.55271E-15. Any ideas why?

0.94
-5
5
25
-25
3.06
-25
21
0

I have tried it with the SUM cell formatted as number to two decimals and it
worked fine - 0.00
(Excel 2003)
--
Traa Dy Liooar

Jock


"Perplexed" wrote:

> The sum of these numbers should be zero (0). However, no matter what the
> cell format, the following numbers add up to 3.55271E-15. Any ideas why?
>
> 0.94
> -5
> 5
> 25
> -25
> 3.06
> -25
> 21
> 0
>

Fixed point binary representation. Try to calculate the exact binary
representation of 0.94 (or of 3.06).
Same reason that you may get similar small rounding errors with a decimal
calculation if you've got 10/3 + 10/3 + 10/3 and try to subtract 10 from the
result.
--
David Biddulph

"Perplexed" <(E-Mail Removed)> wrote in message
news:4A5787D4-CB44-4DA1-ADED-(E-Mail Removed)...
> The sum of these numbers should be zero (0). However, no matter what the
> cell format, the following numbers add up to 3.55271E-15. Any ideas why?
>
> 0.94
> -5
> 5
> 25
> -25
> 3.06
> -25
> 21
> 0
>

Thanks for the reply. Here's more info on this problem.
1. These cells are summed up in a pivot table where the pivot table has the
format of accounting (to dash out zeros in display).
2. Although the sum of these numbers should be net flat zero, it shows up
as (0.00) because I have a conditional formatting set to show negative
numbers in red 0.00 format.
3. All other zero numbers do show as "-" except for the cell that sums up
these numbers.


"Jock" wrote:

> I have tried it with the SUM cell formatted as number to two decimals and it
> worked fine - 0.00
> (Excel 2003)
> --
> Traa Dy Liooar
>
> Jock
>
>
> "Perplexed" wrote:
>
> > The sum of these numbers should be zero (0). However, no matter what the
> > cell format, the following numbers add up to 3.55271E-15. Any ideas why?
> >
> > 0.94
> > -5
> > 5
> > 25
> > -25
> > 3.06
> > -25
> > 21
> > 0
> >

You can put your calculation in a ROUND function. Round to the number
of digits needed.

Conversely, you can test for the sum being less than the smallest
number you'll accept as not zero and force the calculation to zero if
that occurs. As an example:

=IF(SUM(A1:A8)<.0001,0,SUM(A1:A8))

Mark Lincoln

On Sep 10, 11:08 am, Perplexed <Perple...@discussions.microsoft.com>
wrote:
> Thanks for the reply. Here's more info on this problem.
> 1. These cells are summed up in a pivot table where the pivot table has the
> format of accounting (to dash out zeros in display).
> 2. Although the sum of these numbers should be net flat zero, it shows up
> as (0.00) because I have a conditional formatting set to show negative
> numbers in red 0.00 format.
> 3. All other zero numbers do show as "-" except for the cell that sums up
> these numbers.
>
>
>
> "Jock" wrote:
> > I have tried it with the SUM cell formatted as number to two decimals and it
> > worked fine - 0.00
> > (Excel 2003)
> > --
> > Traa Dy Liooar
>
> > Jock
>
> > "Perplexed" wrote:
>
> > > The sum of these numbers should be zero (0). However, no matter what the
> > > cell format, the following numbers add up to 3.55271E-15. Any ideas why?
>
> > > 0.94
> > > -5
> > > 5
> > > 25
> > > -25
> > > 3.06
> > > -25
> > > 21
> > > 0- Hide quoted text -
>
> - Show quoted text -

Thanks that will come in handy for other need, however, in this case, it
doesn't work because using the Fixed function, the sum function shows 0.

"David Biddulph" wrote:

> Fixed point binary representation. Try to calculate the exact binary
> representation of 0.94 (or of 3.06).
> Same reason that you may get similar small rounding errors with a decimal
> calculation if you've got 10/3 + 10/3 + 10/3 and try to subtract 10 from the
> result.
> --
> David Biddulph
>
> "Perplexed" <(E-Mail Removed)> wrote in message
> news:4A5787D4-CB44-4DA1-ADED-(E-Mail Removed)...
> > The sum of these numbers should be zero (0). However, no matter what the
> > cell format, the following numbers add up to 3.55271E-15. Any ideas why?
> >
> > 0.94
> > -5
> > 5
> > 25
> > -25
> > 3.06
> > -25
> > 21
> > 0
> >
>
>
>

I wasn't suggesting that you should use the Excel FIXED function (which
rounds a number to the specified number of decimals, formats the number in
decimal format using a period and commas, and returns the result as text).

I was saying that in a fixed point binary representation (which is what
Excel and most computing calculations use) you are likely to get rounding
errors when you try to represent decimal numbers. The only decimal numbers
that can be expressed exactly in binary are numbers such as 0.5, 0.25,
0.125, ... and their multiples. Numbers such as 0.1 do not have an exact
binary representation.
--
David Biddulph

"Perplexed" <(E-Mail Removed)> wrote in message
news:EDFF25D1-E72F-4ECD-83BF-(E-Mail Removed)...
> Thanks that will come in handy for other need, however, in this case, it
> doesn't work because using the Fixed function, the sum function shows 0.

> "David Biddulph" wrote:
>
>> Fixed point binary representation. Try to calculate the exact binary
>> representation of 0.94 (or of 3.06).
>> Same reason that you may get similar small rounding errors with a decimal
>> calculation if you've got 10/3 + 10/3 + 10/3 and try to subtract 10 from
>> the
>> result.

>> "Perplexed" <(E-Mail Removed)> wrote in message
>> news:4A5787D4-CB44-4DA1-ADED-(E-Mail Removed)...
>> > The sum of these numbers should be zero (0). However, no matter what
>> > the
>> > cell format, the following numbers add up to 3.55271E-15. Any ideas
>> > why?
>> >
>> > 0.94
>> > -5
>> > 5
>> > 25
>> > -25
>> > 3.06
>> > -25
>> > 21
>> > 0
>> >
>>
>>
>>

This would work but I need to display legitimate negative numbers. Thanks.

"Mark Lincoln" wrote:

> You can put your calculation in a ROUND function. Round to the number
> of digits needed.
>
> Conversely, you can test for the sum being less than the smallest
> number you'll accept as not zero and force the calculation to zero if
> that occurs. As an example:
>
> =IF(SUM(A1:A8)<.0001,0,SUM(A1:A8))
>
> Mark Lincoln
>
> On Sep 10, 11:08 am, Perplexed <Perple...@discussions.microsoft.com>
> wrote:
> > Thanks for the reply. Here's more info on this problem.
> > 1. These cells are summed up in a pivot table where the pivot table has the
> > format of accounting (to dash out zeros in display).
> > 2. Although the sum of these numbers should be net flat zero, it shows up
> > as (0.00) because I have a conditional formatting set to show negative
> > numbers in red 0.00 format.
> > 3. All other zero numbers do show as "-" except for the cell that sums up
> > these numbers.
> >
> >
> >
> > "Jock" wrote:
> > > I have tried it with the SUM cell formatted as number to two decimals and it
> > > worked fine - 0.00
> > > (Excel 2003)
> > > --
> > > Traa Dy Liooar
> >
> > > Jock
> >
> > > "Perplexed" wrote:
> >
> > > > The sum of these numbers should be zero (0). However, no matter what the
> > > > cell format, the following numbers add up to 3.55271E-15. Any ideas why?
> >
> > > > 0.94
> > > > -5
> > > > 5
> > > > 25
> > > > -25
> > > > 3.06
> > > > -25
> > > > 21
> > > > 0- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

Well, of course you do. Leave it to me to ignore the negative numbers
in your example. Let's modify my example:

=IF(ABS(SUM(A1:A8))<.0001,0,SUM(A1:A8))

That should work better. In this example, any sum within +/-.0001 of
zero becomes zero.

Sorry for the confusion.

Mark Lincoln

On Sep 10, 2:30 pm, Perplexed <Perple...@discussions.microsoft.com>
wrote:
> This would work but I need to display legitimate negative numbers. Thanks.
>
>
>
> "Mark Lincoln" wrote:
> > You can put your calculation in a ROUND function. Round to the number
> > of digits needed.
>
> > Conversely, you can test for the sum being less than the smallest
> > number you'll accept as not zero and force the calculation to zero if
> > that occurs. As an example:
>
> > =IF(SUM(A1:A8)<.0001,0,SUM(A1:A8))
>
> > Mark Lincoln
>
> > On Sep 10, 11:08 am, Perplexed <Perple...@discussions.microsoft.com>
> > wrote:
> > > Thanks for the reply. Here's more info on this problem.
> > > 1. These cells are summed up in a pivot table where the pivot table has the
> > > format of accounting (to dash out zeros in display).
> > > 2. Although the sum of these numbers should be net flat zero, it shows up
> > > as (0.00) because I have a conditional formatting set to show negative
> > > numbers in red 0.00 format.
> > > 3. All other zero numbers do show as "-" except for the cell that sums up
> > > these numbers.
>
> > > "Jock" wrote:
> > > > I have tried it with the SUM cell formatted as number to two decimals and it
> > > > worked fine - 0.00
> > > > (Excel 2003)
> > > > --
> > > > Traa Dy Liooar
>
> > > > Jock
>
> > > > "Perplexed" wrote:
>
> > > > > The sum of these numbers should be zero (0). However, no matter what the
> > > > > cell format, the following numbers add up to 3.55271E-15. Any ideas why?
>
> > > > > 0.94
> > > > > -5
> > > > > 5
> > > > > 25
> > > > > -25
> > > > > 3.06
> > > > > -25
> > > > > 21
> > > > > 0- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Thanks all. I solved it for this purpose by using the ROUND() function in
the display since I vlookup the pivot table values. Appreciate all your help.

"Mark Lincoln" wrote:

> Well, of course you do. Leave it to me to ignore the negative numbers
> in your example. Let's modify my example:
>
> =IF(ABS(SUM(A1:A8))<.0001,0,SUM(A1:A8))
>
> That should work better. In this example, any sum within +/-.0001 of
> zero becomes zero.
>
> Sorry for the confusion.
>
> Mark Lincoln
>
> On Sep 10, 2:30 pm, Perplexed <Perple...@discussions.microsoft.com>
> wrote:
> > This would work but I need to display legitimate negative numbers. Thanks.
> >
> >
> >
> > "Mark Lincoln" wrote:
> > > You can put your calculation in a ROUND function. Round to the number
> > > of digits needed.
> >
> > > Conversely, you can test for the sum being less than the smallest
> > > number you'll accept as not zero and force the calculation to zero if
> > > that occurs. As an example:
> >
> > > =IF(SUM(A1:A8)<.0001,0,SUM(A1:A8))
> >
> > > Mark Lincoln
> >
> > > On Sep 10, 11:08 am, Perplexed <Perple...@discussions.microsoft.com>
> > > wrote:
> > > > Thanks for the reply. Here's more info on this problem.
> > > > 1. These cells are summed up in a pivot table where the pivot table has the
> > > > format of accounting (to dash out zeros in display).
> > > > 2. Although the sum of these numbers should be net flat zero, it shows up
> > > > as (0.00) because I have a conditional formatting set to show negative
> > > > numbers in red 0.00 format.
> > > > 3. All other zero numbers do show as "-" except for the cell that sums up
> > > > these numbers.
> >
> > > > "Jock" wrote:
> > > > > I have tried it with the SUM cell formatted as number to two decimals and it
> > > > > worked fine - 0.00
> > > > > (Excel 2003)
> > > > > --
> > > > > Traa Dy Liooar
> >
> > > > > Jock
> >
> > > > > "Perplexed" wrote:
> >
> > > > > > The sum of these numbers should be zero (0). However, no matter what the
> > > > > > cell format, the following numbers add up to 3.55271E-15. Any ideas why?
> >
> > > > > > 0.94
> > > > > > -5
> > > > > > 5
> > > > > > 25
> > > > > > -25
> > > > > > 3.06
> > > > > > -25
> > > > > > 21
> > > > > > 0- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>
>