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
--
Brevity is the soul of wit.

Have you tried:

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

--

HTH,

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

"Dave F" wrote in message
...
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
--
Brevity is the soul of wit.

No I haven't tried that, but I suppose that should have been obvious....thanks.
--
Brevity is the soul of wit.


"RagDyeR" wrote:

Have you tried:

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

--

HTH,

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

"Dave F" wrote in message
...
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
--
Brevity is the soul of wit.

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

"Dave F" wrote:

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
--
Brevity is the soul of wit.

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
--
*****
birds of the same feather flock together..



"Dave F" wrote:

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
--
Brevity is the soul of wit.

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

Sorry about this.
--
Brevity is the soul of wit.


"pinmaster" wrote:

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

"Dave F" wrote:

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
--
Brevity is the soul of wit.

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" wrote:

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
--
*****
birds of the same feather flock together..



"Dave F" wrote:

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
--
Brevity is the soul of wit.

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,

--
*****
birds of the same feather flock together..



"pinmaster" wrote:

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" wrote:

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
--
*****
birds of the same feather flock together..



"Dave F" wrote:

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
--
Brevity is the soul of wit.

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

--
*****
birds of the same feather flock together..



"pinmaster" wrote:

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" wrote:

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
--
*****
birds of the same feather flock together..



"Dave F" wrote:

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
--
Brevity is the soul of wit.