find an average from a range, utilizing all data > 0

C

Chase

I have a row of various #s and I would like to take the average of those
numbers only if they are greater than 0, if they are less than or equal to 0
I would like to omit them.

How can create a formula to reflect this?

Thanks,
CT
 
B

Biff

Hi!

Try one of these:

=SUMIF(A1:G1,">0",A1:G1)/COUNTIF(A1:G1,">0")

This one is an array and must be entered using the key combo of
CTRL,SHIFT,ENTER:

=AVERAGE(IF(A1:G1>0,A1:G1))

Biff
 
R

Ron Coderre

Try something like this:
=SUMIF(B1:H1,">0")/COUNTIF(B1:H1,">0")

Does that help?
 
B

Biff

Actually, the first formula can be written like:

=SUMIF(A1:G1,">0")/COUNTIF(A1:G1,">0")

Biff
 
A

Ashish Mathur

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=average(if((range>0),range))

Regards,

Ashish Mathur
 
Top