for some reason my formulas are returning a $ -0.00 instead of $0.00 like it
has always done before. How on earth do i have a negative zero. Zero is
Zero.......LOL
Please Help

Depends largly on the functions used

For eg the pmt function in excel
PMT(rate,nper,pv,fv,type)

If the Principal amounty is not stated as a - (neg) the pmt amounty will
return a negative.

Another option is a possible rounding error - cell will show 0, but the true
value is -0.25
The later seems more probable as you stated that they used to work?

HTH

"Michele" wrote:

for some reason my formulas are returning a $ -0.00 instead of $0.00 like it
has always done before. How on earth do i have a negative zero. Zero is
Zero.......LOL
Please Help

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

Totaly stumped
unless
are you setting the format via VBA?
in the format block you posted your parenthesis do not match, not sure if
that could be the cause?

"Michele" wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

prentheisis was just a typo on my part. As far as VBA i don't understand what
that is, i just went to the fomula bar and typed in the formula i wanted to
use. I use that formula all the time so i know it by heart.

I appriciate you efforts. Thank you for trying

"steve_doc" wrote:

Totaly stumped
unless
are you setting the format via VBA?
in the format block you posted your parenthesis do not match, not sure if
that could be the cause?

"Michele" wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

out of intrest what is the value of C3
The colum you posted adds up to 0.00 when I tested

"Michele" wrote:

prentheisis was just a typo on my part. As far as VBA i don't understand what
that is, i just went to the fomula bar and typed in the formula i wanted to
use. I use that formula all the time so i know it by heart.

I appriciate you efforts. Thank you for trying

"steve_doc" wrote:

Totaly stumped
unless
are you setting the format via VBA?
in the format block you posted your parenthesis do not match, not sure if
that could be the cause?

"Michele" wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

I put those numbers in a1:a16 and then used
=sum(a1:a16)
and saw $0.00
in the display.

But if I selected the cell and hit F2 (to edit it), then F9 to convert the
formula to a value, I saw:
7.46069872548105E-14
in the formula bar (the cell continued to display $0.00).

JE McGimpsey explains how a computer deals with numbers:
http://mcgimpsey.com/excel/pennyoff.html

Michele wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

--

Dave Peterson

It's an internal rounding error that produces a value (for the example
given) of

-0.0000000000000746069872548105

for the SUM(J7:Jx) part. It's an inherent problem with representing
finite decimal numbers in binary (just like in decimal, nearly ALL
numbers can't be represented in a fixed number of binary digits).

When performing operations on currency it's usually helpful to use
ROUND(xxxx,2) to ensure that those small errors are discarded.

In article ,
steve_doc wrote:

Totaly stumped
unless
are you setting the format via VBA?
in the format block you posted your parenthesis do not match, not sure if
that could be the cause?

"Michele" wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

C3 is the starting balance of zero........LOL

"steve_doc" wrote:

out of intrest what is the value of C3
The colum you posted adds up to 0.00 when I tested

"Michele" wrote:

prentheisis was just a typo on my part. As far as VBA i don't understand what
that is, i just went to the fomula bar and typed in the formula i wanted to
use. I use that formula all the time so i know it by heart.

I appriciate you efforts. Thank you for trying

"steve_doc" wrote:

Totaly stumped
unless
are you setting the format via VBA?
in the format block you posted your parenthesis do not match, not sure if
that could be the cause?

"Michele" wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

The easiest way for you (and other contributors to the newsgroup) to avoid
such typos is to use the copy and paste facility, whether from your Excel to
the group, or from suggestions in the group back to your Excel. It is
frustrating for questioners and for those trying to answer when they find
that a lot of time is wasted by unnecessary typos.
Not a criticism of you, Michele, but advice for future contributors.
--
David Biddulph

"Michele" wrote in message
...
prentheisis was just a typo on my part. As far as VBA i don't understand
what
that is, i just went to the fomula bar and typed in the formula i wanted
to
use. I use that formula all the time so i know it by heart.

I appriciate you efforts. Thank you for trying

"steve_doc" wrote:

Totaly stumped
unless
are you setting the format via VBA?
in the format block you posted your parenthesis do not match, not sure if
that could be the cause?

"Michele" wrote:
....
the format i have used is in these cells is: $ #,##0.00;[Red}-$
#,##0.00

i have triple checked my numbers and can't find any that are not what they
shold be. Is there a way to find out which one is causing the prob.

"Dave Peterson" wrote:

I put those numbers in a1:a16 and then used
=sum(a1:a16)
and saw $0.00
in the display.

But if I selected the cell and hit F2 (to edit it), then F9 to convert the
formula to a value, I saw:
7.46069872548105E-14
in the formula bar (the cell continued to display $0.00).

JE McGimpsey explains how a computer deals with numbers:
http://mcgimpsey.com/excel/pennyoff.html

Michele wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

--

Dave Peterson

On Jun 18, 6:48 am, Michele wrote:
i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts
[see below]

That proves nothing to me. First, the arithmetic above could
introduce rounding error. Second, you do not know say how the column
of numbers that are summed is created. Perhaps those cells have
rounding errors.

The real proof is to format the cell with the formula above using
Scientific format and 14 or 15 decimal places. Actually, even that is
not "real proof" since neither format is a truly accurate picture of
the internal binary number. But they usually expose the internal
rounding error.

When I cut and paste the column of numbers that you posted, then
compute =SUM(A1:A16), the result is (positive) 0.00 when formatted as
Number with 2 decimal places. But it is about 7E-14 when formatted as
Scientific with 14 decimal places.

I'm not sure why you see -0.00. I can only guess that the numbers
that you posted were not entered directly, and the rounding error in
some of those cells tips the sum in the negative direction.

This is an annoying property of binary computers: they cannot store
even the simplest numbers accurately, for the most part. So there is
almost always some miniscule rounding error. Some work-arounds:

(1) Compute =round(C3-SUM(...),2)

(2) Set the Calculation option "Precision as displayed".

Personally, I do not like #2. It can have unforeseeable consequences.

Theoretically, even #1 should not be guaranteed to solve the problem.
I can only guess that it triggers internal heuristics that try to root
out the rounding error inherent in binary computers.

As an aside....

Earlier you wrote:
Zero is Zero.......LOL

Not a ones-complement computer like the CDC 6400 (circa 1960s) ;-).
But that's beside the point.


----- complete previous posting -----

On Jun 18, 6:48 am, Michele wrote:
i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be a
rounding error.
Thank you all for helping me try to figure this out. i'm not argueing with
anyone, just trying to understand.

Hi Michele

I think JE McGimpsey summed it up perfectly

"for the SUM(J7:Jx) part. It's an inherent problem with representing
finite decimal numbers in binary (just like in decimal, nearly ALL
numbers can't be represented in a fixed number of binary digits).

When performing operations on currency it's usually helpful to use
ROUND(xxxx,2) to ensure that those small errors are discarded."


"Michele" wrote:

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be a
rounding error.
Thank you all for helping me try to figure this out. i'm not argueing with
anyone, just trying to understand.

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be
a
rounding error.

I don't have an answer to **why** it is happening, but that ending balance
looks like it's a Single data type rather than a Double data type. When a
Single is coerced to a Double, the extra precision required by the Double is
just junk (I think the Double inherits the Single's value plus whatever is
in the "extra" bit locations taken over for storing the Double that is was
not supplied by the Single). Do you have an macros running as part of this
calculation?

Rick

In article ,
Michele wrote:

i have triple checked my numbers and can't find any that are not what they
shold be. Is there a way to find out which one is causing the prob.

There isn't a particular number that's causing your problem. It's a
problem with the fact that decimal numbers can't always be represented
by a fixed number of binary digits, which is what the math processor
deals with. So when the math processor has to work with these numbers
with slight errors in representation, the result often has a slight
error in representation.

This isn't a problem if you just want to display the number - the
display engine will round it for you.

But XL has no way of knowing, when you compare the value to zero,
whether that slight error is significant to you or not. With currency,
a value of that magnitude certainly is insignificant. With engineering
calculations - perhaps or perhaps not.


There is no way to prevent this other than using integers - It's
inherent in all computers and all spreadsheets. So one deals with it by
using ROUND(xxx,2) for currency.

ok so how would i put that in my formula? i looked and tried a few times, i
can get it to round but not add and round.

=C3-SUM(J7:J65536)

"steve_doc" wrote:

Hi Michele

I think JE McGimpsey summed it up perfectly

"for the SUM(J7:Jx) part. It's an inherent problem with representing
finite decimal numbers in binary (just like in decimal, nearly ALL
numbers can't be represented in a fixed number of binary digits).

When performing operations on currency it's usually helpful to use
ROUND(xxxx,2) to ensure that those small errors are discarded."


"Michele" wrote:

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be a
rounding error.
Thank you all for helping me try to figure this out. i'm not argueing with
anyone, just trying to understand.

All XL numeric values are doubles.

The problem is inherent in double precision floating point math.

See

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

for more.

In article ,
"Rick Rothstein \(MVP - VB\)"
wrote:

I don't have an answer to **why** it is happening, but that ending balance
looks like it's a Single data type rather than a Double data type. When a
Single is coerced to a Double, the extra precision required by the Double is
just junk (I think the Double inherits the Single's value plus whatever is
in the "extra" bit locations taken over for storing the Double that is was
not supplied by the Single). Do you have an macros running as part of this
calculation?

No
no maco or anything else running

"Rick Rothstein (MVP - VB)" wrote:

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be
a
rounding error.

I don't have an answer to **why** it is happening, but that ending balance
looks like it's a Single data type rather than a Double data type. When a
Single is coerced to a Double, the extra precision required by the Double is
just junk (I think the Double inherits the Single's value plus whatever is
in the "extra" bit locations taken over for storing the Double that is was
not supplied by the Single). Do you have an macros running as part of this
calculation?

Rick

One way:

=ROUND(C3-SUM(J7:J65536),2)



In article ,
Michele wrote:

ok so how would i put that in my formula? i looked and tried a few times, i
can get it to round but not add and round.

=C3-SUM(J7:J65536)

THANK YOU EVERONE FOR ALL YOU HELP

All XL numeric values are doubles.

The problem is inherent in double precision floating point math.

No, I am aware of this problem (which usually only affects the last 2 of 3
decimal digits in a Double)... what happened is I looked at the number
Michele posted and, without counting to see the first non-zero digit was in
the 14th decimal digit, saw half the number was affected by the problem and
thought 8 zeroes followed by 8 garbage digits... it looked like the old
Single-to-Double coercion problem.

Rick

The only number that NOT "causing" the problem is 400. The only 2-place
decimal fractions that CAN be exactly represented in binary are .00, .25,
..50, and .75. The rest must be approximated, and will have non-zero figures
beyond the 15th figure. As documented, Excel will not display more than 15
figures (as you found, it will just show zeros instead of what is really
there, if you ask for more than 15 figures).

For example, the actual decimal value of the binary approximation to 923.45
is 923.450000000000045474735088646411895751953125.

Jerry

"Michele" wrote:

i have triple checked my numbers and can't find any that are not what they
shold be. Is there a way to find out which one is causing the prob.

"Dave Peterson" wrote:

I put those numbers in a1:a16 and then used
=sum(a1:a16)
and saw $0.00
in the display.

But if I selected the cell and hit F2 (to edit it), then F9 to convert the
formula to a value, I saw:
7.46069872548105E-14
in the formula bar (the cell continued to display $0.00).

JE McGimpsey explains how a computer deals with numbers:
http://mcgimpsey.com/excel/pennyoff.html

Michele wrote:

i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00

--

Dave Peterson