enter following numbers in worksheet (in same sequence)
14189.05
-13605.77
-544.23
-39.05
sum of above
The answer(sum) should be exactly zero. But the answer computed and
displayed is -1.1795E-12
Now change the sequence of numbers and sum them. Answer will be exactly zero.
Any ideas, whats the reason.
Any solution to overcome this - I want to reach an answer of exactly zero
(without changing the sequence).
There are many other combinations of sums in which there is a similar problem.
Please help.

http://www.cpearson.com/excel/rounding.htm


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


--
Regards,

Peo Sjoblom



"Rajneesh Arora" <(E-Mail Removed)> wrote in message
news:A15FB171-A655-44F0-A5FF-(E-Mail Removed)...
> enter following numbers in worksheet (in same sequence)
> 14189.05
> -13605.77
> -544.23
> -39.05
> sum of above
> The answer(sum) should be exactly zero. But the answer computed and
> displayed is -1.1795E-12
> Now change the sequence of numbers and sum them. Answer will be exactly
> zero.
> Any ideas, whats the reason.
> Any solution to overcome this - I want to reach an answer of exactly zero
> (without changing the sequence).
> There are many other combinations of sums in which there is a similar
> problem.
> Please help.

Hi,

seems it would be possible just by rounding like:

=ROUND(SUM(your range),0)

Thanks,
--
Farhad Hodjat


"Rajneesh Arora" wrote:

> enter following numbers in worksheet (in same sequence)
> 14189.05
> -13605.77
> -544.23
> -39.05
> sum of above
> The answer(sum) should be exactly zero. But the answer computed and
> displayed is -1.1795E-12
> Now change the sequence of numbers and sum them. Answer will be exactly zero.
> Any ideas, whats the reason.
> Any solution to overcome this - I want to reach an answer of exactly zero
> (without changing the sequence).
> There are many other combinations of sums in which there is a similar problem.
> Please help.

change the format of all the cells including result to Number with 2
decimals. It will be zero then. I suppose currently the cells are of general
format.

The sum was 0 on my PC after I changed the formatting.
Hope this helps.
--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"Rajneesh Arora" wrote:

> enter following numbers in worksheet (in same sequence)
> 14189.05
> -13605.77
> -544.23
> -39.05
> sum of above
> The answer(sum) should be exactly zero. But the answer computed and
> displayed is -1.1795E-12
> Now change the sequence of numbers and sum them. Answer will be exactly zero.
> Any ideas, whats the reason.
> Any solution to overcome this - I want to reach an answer of exactly zero
> (without changing the sequence).
> There are many other combinations of sums in which there is a similar problem.
> Please help.

No it wasn't, formatting only changes the display so if you format
1.00000002 to 2 decimals it will display as 1.00 but it will still be
1.00000002


You can select precision as displayed under tools>options>calculations but
that can affect other parts of the spreadsheet



--
Regards,

Peo Sjoblom



"Pranav Vaidya" <(E-Mail Removed)> wrote in message
news:202DD1B9-BEAF-49F1-BC7C-(E-Mail Removed)...
> change the format of all the cells including result to Number with 2
> decimals. It will be zero then. I suppose currently the cells are of
> general
> format.
>
> The sum was 0 on my PC after I changed the formatting.
> Hope this helps.
> --
> Pranav Vaidya
> VBA Developer
> PN, MH-India
> If you think my answer is useful, please rate this post as an ANSWER!!
>
>
> "Rajneesh Arora" wrote:
>
>> enter following numbers in worksheet (in same sequence)
>> 14189.05
>> -13605.77
>> -544.23
>> -39.05
>> sum of above
>> The answer(sum) should be exactly zero. But the answer computed and
>> displayed is -1.1795E-12
>> Now change the sequence of numbers and sum them. Answer will be exactly
>> zero.
>> Any ideas, whats the reason.
>> Any solution to overcome this - I want to reach an answer of exactly zero
>> (without changing the sequence).
>> There are many other combinations of sums in which there is a similar
>> problem.
>> Please help.