sum shows (0.00)

[sherwin]

Hi

How come I get a (0.00) for this calculation:

9528.27
(275.92)
1041.34
374.88
(43033.68)
1780.70
1110.76
3059.74
4755.90
1907.73
795.62
846.33
1284.32
520.98
4.17
614.20
148.39
7239.08
4381.63
377.31
1437.05
124.96
88.17
1457.87
3.47
42.34
219.17
9.02
156.20

Format is in Numbers (12345), 2 decimal places, with separator
No formulas involved, no rounding off, I just typed them as it is.
Precision displayed doesn't help
When I add decimal places, I would get (0.000000000010658141),
sometimes(0.00000000000250111043), why does this happen?
HELP ANYONE.

[Duncan]

Sherwin,

The numbers in brackets are treated as Minus Figures.

Why are they in brackets?

Duncan


sherwin wrote:

> Hi
>
> How come I get a (0.00) for this calculation:
>
> 9528.27
> (275.92)
> 1041.34
> 374.88
> (43033.68)
> 1780.70
> 1110.76
> 3059.74
> 4755.90
> 1907.73
> 795.62
> 846.33
> 1284.32
> 520.98
> 4.17
> 614.20
> 148.39
> 7239.08
> 4381.63
> 377.31
> 1437.05
> 124.96
> 88.17
> 1457.87
> 3.47
> 42.34
> 219.17
> 9.02
> 156.20
>
> Format is in Numbers (12345), 2 decimal places, with separator
> No formulas involved, no rounding off, I just typed them as it is.
> Precision displayed doesn't help
> When I add decimal places, I would get (0.000000000010658141),
> sometimes(0.00000000000250111043), why does this happen?
> HELP ANYONE.

[Rich]

the numbers in brackets are causing the calculation error

if they are negative numbers use a - instead of the first bracket and do not
use the final bracket



"sherwin" wrote:

> Hi
>
> How come I get a (0.00) for this calculation:
>
> 9528.27
> (275.92)
> 1041.34
> 374.88
> (43033.68)
> 1780.70
> 1110.76
> 3059.74
> 4755.90
> 1907.73
> 795.62
> 846.33
> 1284.32
> 520.98
> 4.17
> 614.20
> 148.39
> 7239.08
> 4381.63
> 377.31
> 1437.05
> 124.96
> 88.17
> 1457.87
> 3.47
> 42.34
> 219.17
> 9.02
> 156.20
>
> Format is in Numbers (12345), 2 decimal places, with separator
> No formulas involved, no rounding off, I just typed them as it is.
> Precision displayed doesn't help
> When I add decimal places, I would get (0.000000000010658141),
> sometimes(0.00000000000250111043), why does this happen?
> HELP ANYONE.
>
>

[sherwin]

Hi Rich & Duncan!

First of all, thanks very much for the reply, I am new to this thing at
google groups. I was in doubt no one would reply to my dilemma (I know
it's not much of a dilemma) and that it would take days before someone
would answer, thanks again.

Anyway, I tried using the - instead of the brackets and it sure did
showed 0.00. My company is using excel for it's report, and it's the
format they've been using, I just type in the figures, any more
solution you can recommend. The report would show something like this:

NLC CC ACCOUNT TITLE TOTAL
(USD)

3100 Purchase ledger control (43,033.68)
7800 302 Carriage & freight 1,780.70

It has to show the brackets.


Rich wrote:
> the numbers in brackets are causing the calculation error
>
> if they are negative numbers use a - instead of the first bracket and do not
> use the final bracket
>

[sherwin]

They are in brackets to show whether debit/credit balances.

[Duncan]

Sherwin

Array enter this Formula after changing the range of summed figures to
whatever your range is (at the moment i put D1:D25)

Array enter means put the formula into the cell via the formula bar at
the top, and instead of pressing enter press CTRL+SHIFT+ENTER which
will put curly brackets around it

=SUM(ABS(D1:D25))


Let us know if this works for you

What is is doing is using the worksheet function ABS (or Absolute)
which ignores minus figures, bear this in mind in case some of your
figures really do need to be a minus

HTH

Duncan



> Hi Rich & Duncan!
>
> First of all, thanks very much for the reply, I am new to this thing at
> google groups. I was in doubt no one would reply to my dilemma (I know
> it's not much of a dilemma) and that it would take days before someone
> would answer, thanks again.
>
> Anyway, I tried using the - instead of the brackets and it sure did
> showed 0.00. My company is using excel for it's report, and it's the
> format they've been using, I just type in the figures, any more
> solution you can recommend. The report would show something like this:
>
> NLC CC ACCOUNT TITLE TOTAL
> (USD)
>
> 3100 Purchase ledger control (43,033.68)
> 7800 302 Carriage & freight 1,780.70
>
> It has to show the brackets.
>
>
> Rich wrote:
> > the numbers in brackets are causing the calculation error
> >
> > if they are negative numbers use a - instead of the first bracket and do not
> > use the final bracket
> >

[Duncan]

Sherwin,

Following on from my last post, this might be handy for your reference
and future use of arrays, they still confuse me really!

http://www.meadinkent.co.uk/xlarrays.htm

Best Regards

Duncan


sherwin wrote:

> They are in brackets to show whether debit/credit balances.

[Rich]

if the number in brackets is not to be included in the count have a look at
the sumif command



"sherwin" wrote:

> They are in brackets to show whether debit/credit balances.
>
>

[sherwin]

It didn't work, it added all numbers disregarding the negative numbers,
i got a 86369.28
rather than 0.00. Did you get a 0.00 when you did it, maybe I did
something wrong.

But that was added knowledge, I didn't know ABS, array formula &
Ctrl+shift+enter. Thanks.

[sherwin]

numbers in brackets are included, that's why i should get a 0.00.

Rich wrote:
> if the number in brackets is not to be included in the count have a look at
> the sumif command
>
>
>
> "sherwin" wrote:
>
> > They are in brackets to show whether debit/credit balances.
> >
> >

[sherwin]

I don't get it, why would excel have a negative difference in the
decimals (0.0000000001812125, sample only), when I enter figures with
two decimal places only, doesn't it read the number as it is?

[sherwin]

isn't it the same thing as:

10.00
(40.00)
25.00
5.00

0.00

[Duncan]

Sherwin,

I hope you are still following this thread as i only log in once or
twice a day but havent forgotten it!

I seem to have misunderstood what you wanted, my array formula should
convert all the ones in brackets into +numbers and sum EVERYTHING as a
plus. I thought this was what you wanted. if it is what you wanted but
isnt working properly then let me know, there must be a problem
somewhere!

If you wanted the numbers in brackets to remain as a minus figure then
just sum the column, (ps, when i put brackets into excel like (1) it
instantly changes it to -1, does yours do this?

Anyway, I am going to read your posts a few times more and try to
fathom the problem again because I am slightly confused and really want
for you to be left with a solution.

Regards

Duncan


sherwin wrote:

> isn't it the same thing as:
>
> 10.00
> (40.00)
> 25.00
> 5.00
>
> 0.00

[Duncan]

Sherwin,

I think i understand now, I got lost on a tangent when i hadnt
understood your very first post.

What you actually want is the infinite precision to be correct always,
and the problem is caused because for some reason in brackets as a
minus is different to preceded by a minus figure. Correct me if im
wrong.

I thought you wanted the brackets there for visual effect but actually
wanted the numbers NOT minus, I was mistaken I think.

In this case, I am not sure how to get the precision accurate at all
times without A) typing a minus and lose the brackets or B) use a macro
behind a button to perform the sum.

If im wrong again, please let me know!, having a bad week I think for
misunderstanding and I am truly sorry if I have sent on a wild goose
chase.

Regards

Duncan


Duncan wrote:

> Sherwin,
>
> I hope you are still following this thread as i only log in once or
> twice a day but havent forgotten it!
>
> I seem to have misunderstood what you wanted, my array formula should
> convert all the ones in brackets into +numbers and sum EVERYTHING as a
> plus. I thought this was what you wanted. if it is what you wanted but
> isnt working properly then let me know, there must be a problem
> somewhere!
>
> If you wanted the numbers in brackets to remain as a minus figure then
> just sum the column, (ps, when i put brackets into excel like (1) it
> instantly changes it to -1, does yours do this?
>
> Anyway, I am going to read your posts a few times more and try to
> fathom the problem again because I am slightly confused and really want
> for you to be left with a solution.
>
> Regards
>
> Duncan
>
>
> sherwin wrote:
>
> > isn't it the same thing as:
> >
> > 10.00
> > (40.00)
> > 25.00
> > 5.00
> >
> > 0.00

[NickHK]

Sherwin,
I think you need to use Formatting to show the brackets, rather than enter
them yourself.
That way Excel "knows" that the number is really -100 in any calculations,
but displays it as (100).
When formatted "General", if I enter (10), Excel changes it to -10 anyway.
e.g.

General Text Number
10 10 10.00
-10 (10) (10.00)
0 10 0.00

So I would guess your cells are formatted as Text.
Change to "Number", with the example of "(1234.00)" and enter the numbers as
e.g -10.

NickHK

"sherwin" <[email protected]> wrote in message
news:[email protected]...
> numbers in brackets are included, that's why i should get a 0.00.
>
> Rich wrote:
> > if the number in brackets is not to be included in the count have a look
at
> > the sumif command
> >
> >
> >
> > "sherwin" wrote:
> >
> > > They are in brackets to show whether debit/credit balances.
> > >
> > >
>

[NickHK]

Sherwin,
OK, ignore this. I didn't read the start of the thread.
What you see is normal with floating point arithmetic:
http://docs.sun.com/source/806-3568/ncg_goldberg.html
or a more VB friendly version:
http://vb-helper.com/tutorial_floating_point.html

So you need to decide on the degree of precision that is acceptable to you
Either:
=If(Sum(YourRange)<0.00005,"OK","Discrepency")
Or look into Round and related functions.

NickHK

"NickHK" <[email protected]> wrote in message
news:u4jI8%[email protected]...
> Sherwin,
> I think you need to use Formatting to show the brackets, rather than enter
> them yourself.
> That way Excel "knows" that the number is really -100 in any calculations,
> but displays it as (100).
> When formatted "General", if I enter (10), Excel changes it to -10 anyway.
> e.g.
>
> General Text Number
> 10 10 10.00
> -10 (10) (10.00)
> 0 10 0.00
>
> So I would guess your cells are formatted as Text.
> Change to "Number", with the example of "(1234.00)" and enter the numbers
as
> e.g -10.
>
> NickHK
>
> "sherwin" <[email protected]> wrote in message
> news:[email protected]...
> > numbers in brackets are included, that's why i should get a 0.00.
> >
> > Rich wrote:
> > > if the number in brackets is not to be included in the count have a
look
> at
> > > the sumif command
> > >
> > >
> > >
> > > "sherwin" wrote:
> > >
> > > > They are in brackets to show whether debit/credit balances.
> > > >
> > > >
> >
>
>

[Jerry W. Lewis]

Excel's math is correct, given the unavoidable approximations to your inputs.
The usual solution is to round the result.

Most terminating decimal fractions are non-terminating binary fractions that
can only be approximated in finite precision. In fact, the only 2 decimal
place numbers that can be exactly represented are .00, .25, .50, and .75.

To understand why, note that you would see the same issue in decimal if you
were working with thirds. For instance if 4 figures were carried in decimal
calculations, then 1/3 +1/3 +1/3 -1 = 0.3333+0.3333+0.3333-1 = 0.9999-1 =
-0.0001

If you want to learn more about binary approximations to decimal numbers,
you might find the functions at
http://groups.google.com/group/micro...06871cf92f8465
to be helpful.

Jerry

"sherwin" wrote:

> Hi
>
> How come I get a (0.00) for this calculation:
>
> 9528.27
> (275.92)
> 1041.34
> 374.88
> (43033.68)
> 1780.70
> 1110.76
> 3059.74
> 4755.90
> 1907.73
> 795.62
> 846.33
> 1284.32
> 520.98
> 4.17
> 614.20
> 148.39
> 7239.08
> 4381.63
> 377.31
> 1437.05
> 124.96
> 88.17
> 1457.87
> 3.47
> 42.34
> 219.17
> 9.02
> 156.20
>
> Format is in Numbers (12345), 2 decimal places, with separator
> No formulas involved, no rounding off, I just typed them as it is.
> Precision displayed doesn't help
> When I add decimal places, I would get (0.000000000010658141),
> sometimes(0.00000000000250111043), why does this happen?
> HELP ANYONE.
>
>