Hey brainiacs! I have this odd formatting/COUNTIF problem and I hope you
can help. I have 3 columns of numbers - all in plain US Dollars with
whole cents, i.e. $5327.42

Each column is about 400 items, one is formatted in negative numbers,
the other (to it's right) with positive numbers, and most (but not all)
of the numbers match. They all look like regular dollar amounts when
each cell is examined.

I wanted to find variances and did that with a simple sum of Col. 1 +
Col. 2 = $0.00 in Col. 3, with any non-zero number sticking out.

Here's the problem. I checked this Col 3 list and found 11 non-zero
items. When I use COUNTIF, i.e.:
=COUNTIF(O4:O413,"0")+COUNTIF(O4:O413,"<0") I get a count of 12!!!

I copied & pasted all numbers from Col 3 as Values and found one is
4.547 to the -13. but all numbers are formatted as currency with 2
decimal places, BTW that one cell reads $0.00 as a sum of 3 numbers.
Can ANYONE tell me why this is happening, and how I can avoid this?? I
feel like I'm in the Twilight Zone here.... Thanks.

Mark
Boston


--
LTUser54
------------------------------------------------------------------------
LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459
View this thread: http://www.excelforum.com/showthread...hreadid=545250

See:

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

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

Regards,

Peo Sjoblom

"LTUser54" wrote:


Hey brainiacs! I have this odd formatting/COUNTIF problem and I hope you
can help. I have 3 columns of numbers - all in plain US Dollars with
whole cents, i.e. $5327.42

Each column is about 400 items, one is formatted in negative numbers,
the other (to it's right) with positive numbers, and most (but not all)
of the numbers match. They all look like regular dollar amounts when
each cell is examined.

I wanted to find variances and did that with a simple sum of Col. 1 +
Col. 2 = $0.00 in Col. 3, with any non-zero number sticking out.

Here's the problem. I checked this Col 3 list and found 11 non-zero
items. When I use COUNTIF, i.e.:
=COUNTIF(O4:O413,"0")+COUNTIF(O4:O413,"<0") I get a count of 12!!!

I copied & pasted all numbers from Col 3 as Values and found one is
4.547 to the -13. but all numbers are formatted as currency with 2
decimal places, BTW that one cell reads $0.00 as a sum of 3 numbers.
Can ANYONE tell me why this is happening, and how I can avoid this?? I
feel like I'm in the Twilight Zone here.... Thanks.

Mark
Boston


--
LTUser54
------------------------------------------------------------------------
LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459
View this thread: http://www.excelforum.com/showthread...hreadid=545250

Peo,

As usual, you impress me with your ability to find a solution! Perhaps
if I weren't quite so brain dead, I would have arrived at that. Still,
using Round along with =COUNTIF(O4:O413,"0")+COUNTIF(O4:O413,"<0") and
then trying futilely to actually have it work probably would have put me
in quite the mood...

I used Tools/Options/Calculation and ended up with a Zero value in the
cell that was previously driving me nuts. Yeah!!!

Thanks, again!

Mark
Boston, MA


--
LTUser54
------------------------------------------------------------------------
LTUser54's Profile: http://www.excelforum.com/member.php...o&userid=33459
View this thread: http://www.excelforum.com/showthread...hreadid=545250