COUNTIF with non-zero format?

[LTUser54]

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

[Peo Sjoblom]

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
>
>

[LTUser54]

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