COUNTIF absolute value of cells in a range are > 0

[Dave F]

How would I use COUNTIF to count only those cells whose absolute values are
greater than 0?

This is my formula: =SUM(H5:H103)/COUNTIF(H5:H103,">0") But there are
negative numbers in the range which should be counted as well as the positive
numbers. I only want to exclude 0 values.

Thanks,

Dave

 

[RagDyeR]

Have you tried:

=SUM(H5:H103)/COUNTIF(H5:H103,"<>0")
?

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

How would I use COUNTIF to count only those cells whose absolute values are
greater than 0?

This is my formula: =SUM(H5:H103)/COUNTIF(H5:H103,">0") But there are
negative numbers in the range which should be counted as well as the
positive
numbers. I only want to exclude 0 values.

Thanks,

Dave

 

[Dave F]

No I haven't tried that, but I suppose that should have been obvious....thanks.

 

[pinmaster]

Hi Dave

Not sure what is the purpose of your post since you probably know the answer
but:

COUNTIF(H5:H103,"<>0")

hope I passed the test

Regards!
Jean-Guy

 

[driller]

by trying to figure out the complete formula u have
it looks clear u need an average function

=AVERAGE(IF((H5:H10<>0),(H5:H10<>0)*H5:H10))
hit ctrl-shft-enter

surely u know this especially when u want to consider a BLANK CELL as not
part of the count and has an If 0 value...data with text can be trapped since
the result will be an error - good time to fix the data..
regards

 

[Dave F]

The purpose of the post is that I was not thinking. Yes, the answer is
obvious.

Sorry about this.

 

[pinmaster]

Hi,

Blank cells and text do not affect an average formula so it could be
simplified to:

=AVERAGE(IF(H5:H103<>0,H5:H103))
enter using Ctrl+Shift+Enter


Regards!
Jean-Guy

 

[driller]

thats right again...
=AVERAGE(IF(H5:H103<>0,H5:H103))
with c-s-e
if strickly not need to count a text somewhere in the data, and without
bothering to check if there are some invalid data.
regards2,

 

[driller]

i forgot that the absolute value of
cell A1= k
k>0 = true