Formula counting average, eliminating the 0's

[alainess]

I haven't used this formula in a while and forgot how to. Can you please remind on how to include the formula to count the average, however elimninate the cells that are at 0? Was it something like this:
=average(if>0, M6:P6) ?

Please let me know. Thanks.

 

[Trevor G]

Welcome to the Forum,

Try something like this

=AVERAGEIF(M6:P6,">0",M6:P6)

 

[njimack]

You were almost there.

=AVERAGE(IF(M6:P6>0,M6:P6))
(confirmed with ctrl+shift+enter)

 

[alainess]

Hi, I tried the formulas that you both provided and there is still an error. they didn't work. I think there is one small error somewhere in yours Neil? Not sure what it is.

 

[njimack]

Hi, I tried the formulas that you both provided and there is still an error. they didn't work. I think there is one small error somewhere in yours Neil? Not sure what it is.

Nope, it works.

Did you confirm with ctrl+Shift+Enter?

 

[Trevor G]

Hi, I tried the formulas that you both provided and there is still an error. they didn't work. I think there is one small error somewhere in yours Neil? Not sure what it is.

Are all your entries numbers?

Excel Workbook
	M	N	O	P	Q
6	12	0	9	7	9.333333
Sheet1

 

[T. Valko]

Hi, I tried the formulas that you both provided and there is still an error. they didn't work. I think there is one small error somewhere in yours Neil? Not sure what it is.

What version of Excel are you using?

The AVERAGEIF function requires that you are using Excel 2007 or later.

The AVERAGE(IF array formula will work in any version of Excel.

 

[Aladin Akyurek]

I haven't used this formula in a while and forgot how to. Can you please remind on how to include the formula to count the average, however elimninate the cells that are at 0? Was it something like this:
=average(if>0, M6:P6) ?

Please let me know. Thanks.

Excel 2007 or later...

=AVERAGEIF(M6:P6,">0",M6:P6)

Otherwise, all versions:

=AVERAGE(IF(ISNUMBER(M6:P6),IF(M6:P6>0,M6:P6)))

which requires control+shift+enter, not just enter.

=SUMIF(M6:P6,">0",M6:P6)/COUNTIF(P6:M6,">0")

 

[alainess]

Yes, my entries are all numbers, however numbers that are transfered over into the cells from other pages. I don't think that should make a difference?
Also, when I hit trhe CTRL, shift, enter, the message simply states that my formula is incorrect and to click on help or function assistant.
Unfortunately, my Excel is in French on my work laptop, however I simply replace the English wording with the correct French wording. It might even accept the English wording. Not sure?

 

[Aladin Akyurek]

Yes, my entries are all numbers, however numbers that are transfered over into the cells from other pages. I don't think that should make a difference?
Also, when I hit trhe CTRL, shift, enter, the message simply states that my formula is incorrect and to click on help or function assistant.
Unfortunately, my Excel is in French on my work laptop, however I simply replace the English wording with the correct French wording. It might even accept the English wording. Not sure?

Control+shift+enter, not just enter:

=MOYENNE(SI(ESTNUM(M6:P6);SI(M6:P6>0;M6:P6)))

Just enter:

=SOMME.SI(M6:P6;">0";M6:P6)/NB.SI(P6:M6;">0")

On newer Excel versions, probably:

=MOYENNE.SI(M6:P6;">0";M6:P6)