Average function across different columns

[ngs007]

Hi

Thought I had sorted this but now as in example below I cannot get average correct across columns (with other data in between them) because of zero values. So in example below the average should be two but it is coming out as 0.67. I have tried average if but cant seem to get it to work:

A B C D E F G H I J J
[TABLE="width: 898"]
<colgroup><col><col span="12"></colgroup><tbody>[TR]
[TD]Teacher[/TD]
[TD]Subject[/TD]
[TD]Number of observations[/TD]
[TD]Average Grade[/TD]
[TD] [/TD]
[TD]Number of observations[/TD]
[TD]Average Grade[/TD]
[TD] [/TD]
[TD]Number of observations[/TD]
[TD]Average Grade[/TD]
[TD] [/TD]
[TD]Total Number of observations[/TD]
[TD]Average Grade[/TD]
[/TR]
[TR]
[TD]pete beal[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]2.00[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]0.00[/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]0.67[/TD]
[/TR]
</tbody>[/TABLE]

Solution?

 

[Ron Coderre]

Using your posted data in A1:J2, here are 2 approaches:
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)
returns the average of the non-zero average grades:

J2: =AVERAGE(IF(($C$1:$H$1="Average Grade")*(C2:H2<>0),C2:H2))

or...this regular formula

J2: =SUMIF(B1:H1,"Average Grade",B2:H2)/SUMPRODUCT((B1:H1="Average Grade")*(B2:H2<>0)

)

In your example, both formulas return: 2

Is that something you can work with?

 

[ngs007]

Hi Yes
It works a treat - thank you very much - post closed

 

[VoG]

Cross posted average of specific cells in various columns

 

[ngs007]

Apologies - new to these posts. I understand that in future if I post elsewhere I have to put the url of this too. Will make sure I do so in future. Apologies again.