Answer show red when should be black

[Dave]

Additional info: Tried multiplying both sides of the
formula by -1 and this changes the color of the zeros to
black.

I have a worksheet "error testing formula" that subtracts
one number from another in a cell thats formatted to show
red when the answer is less than zero. If I have not made
an entry error the two numbers are the same so the answer
is zero and the zeros shows up in red. If I reverse the
formula the answer is still zero but shows up in black. My
first thought was that sometimes in Excel during
computations a number might be added to one of the numbers
well to the right of the decimal. I checked for this, to
30 digits past the decimal and it's still all zeros. In
another simular spreadsheet with the same test formula the
answer shows up red when the formula is reversed. Can
someone explain a solution so only red shows when the
answer is less than zero and if the two numbers being
subtracted from each other are the same the zeros will be
black?
Thanks Dave

 

[Earl Kiosterud]

Dave,

If you're using number formatting for the red, give us the code (Format -
Cells - Number - Custom). Or if your result isn't really zero because of
binary-decimal-binary conversions (happens a lot), you can use ROUND() in
your formula. Give us the formula too, and an example of data that fails.

 

[Guest]

Thanks Earl for your help, the "Round" worked, looked at
the Format - Cells - Number - Custom and didn't
understand #,##0,00;[Red]#,##0.00 Could you explain
sequentially what these symbols indicate. Thanks Dave

 

[jeff]

Dave,

You can go to Help (F1) and search on 'custom format' for
exhaustive help.

But as a quickie, (referring to
#,##0.00_);[Red](#,##0.00) ):

- the codes on the left of the ; are for positive numbers,
those on the right for negative.

- the 0.00 indicate that you'll always get 3 digits:
enter 4 and you get 4.00, enter .4 you get 0.40, enter
1.234 and you get 1.23 (rounded up)

- the #,##0 are setting up for millions, thousands,etc
with comma as separators - the #'s show optional digits
as opposed to the 0's just above

- the [Red] indicatest that negative numbers will be in
red font

- the parentheses around the negative numbers indicate
neg numbers within ()s (rather than a negative sign)

- and the _) next to #,##0.00 gives you a space to the
right of the number.

(I think I've gotten this right)
hope this helps
jeff

-----Original Message-----
Thanks Earl for your help, the "Round" worked, looked at
the Format - Cells - Number - Custom and didn't
understand #,##0,00;[Red]#,##0.00 Could you explain
sequentially what these symbols indicate. Thanks Dave

-----Original Message-----
Dave,

If you're using number formatting for the red, give us the code (Format -
Cells - Number - Custom). Or if your result isn't really zero because of
binary-decimal-binary conversions (happens a lot), you can use ROUND() in
your formula. Give us the formula too, and an example

of
data that fails. black.

My

formula

the

will

be


.

.

 

[Earl Kiosterud]

Dave,

The sections Jeff is referring to are separated by semicolons. First for
pisitive numbers, then negatives, then 0, then text. Yours had only two.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Dave,

You can go to Help (F1) and search on 'custom format' for
exhaustive help.

But as a quickie, (referring to
#,##0.00_);[Red](#,##0.00) ):

- the codes on the left of the ; are for positive numbers,
those on the right for negative.

- the 0.00 indicate that you'll always get 3 digits:
enter 4 and you get 4.00, enter .4 you get 0.40, enter
1.234 and you get 1.23 (rounded up)

- the #,##0 are setting up for millions, thousands,etc
with comma as separators - the #'s show optional digits
as opposed to the 0's just above

- the [Red] indicatest that negative numbers will be in
red font

- the parentheses around the negative numbers indicate
neg numbers within ()s (rather than a negative sign)

- and the _) next to #,##0.00 gives you a space to the
right of the number.

(I think I've gotten this right)
hope this helps
jeff

-----Original Message-----
Thanks Earl for your help, the "Round" worked, looked at
the Format - Cells - Number - Custom and didn't
understand #,##0,00;[Red]#,##0.00 Could you explain
sequentially what these symbols indicate. Thanks Dave

-----Original Message-----
Dave,

If you're using number formatting for the red, give us the code (Format -
Cells - Number - Custom). Or if your result isn't really zero because of
binary-decimal-binary conversions (happens a lot), you can use ROUND() in
your formula. Give us the formula too, and an example

of
data that fails.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Additional info: Tried multiplying both sides of the
formula by -1 and this changes the color of the zeros to
black.

I have a worksheet "error testing formula" that subtracts
one number from another in a cell thats formatted to show
red when the answer is less than zero. If I have not made
an entry error the two numbers are the same so the answer
is zero and the zeros shows up in red. If I reverse the
formula the answer is still zero but shows up in

black.

My
first thought was that sometimes in Excel during
computations a number might be added to one of the numbers
well to the right of the decimal. I checked for this, to
30 digits past the decimal and it's still all zeros. In
another simular spreadsheet with the same test

formula

the
answer shows up red when the formula is reversed. Can
someone explain a solution so only red shows when the
answer is less than zero and if the two numbers being
subtracted from each other are the same the zeros

will

be
black?
Thanks Dave


.

.

 

[Jerry W. Lewis]

If you are still interested,
http://groups.google.com/groups?selm=3E7F0390.7070900@no_e-mail.com
may have some useful information.

Negative zero (different from zero, and not just formatting) is defined
in the IEEE 754 standard. In my experience, Excel and VBA each convert
-0 to 0, but it is possible for VBA to return -0 to Excel.

As Earl previously noted, we can only talk in generalities unless you
provide the actual formulas and inputs.

Jerry