Same Data, Same Formula, But different mean.

[Szaboteur]

Hello all,

I do not know where my mind is playing tricks. Mind or math, not sure.

This is the formula that i used to average (with blue color) all the rows and all the columns in the data set (with black collor): =IFERROR(AVERAGEIF(K55:P55,"<>0",K55:P55),"")
But in the same data, if i calculate mean on column this result 4.17. On the other side, when i calculate total mean horizontally, the mean becomes 4.20. In the first place, i thought that the 0 makes the difference. Horizontally to take it as a number and vertically not. But... this is not the answer.

[TABLE="width: 527"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD="align: right"][TABLE="width: 463"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD="align: right"]4.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3.83[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4.17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3.67[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5.00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]4.83[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4.17[/TD]
[TD="align: right"]4.29[/TD]
[TD="align: right"]4.25[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4.38[/TD]
[TD="align: right"]4.43[/TD]
[TD="align: right"]3.83[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]

[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Thank you the one who can help me to clear my mind.

 

[shg]

You can't average averages in that way.

If a class of 20 kids has an average of 80% on tests, and a class of 2 kids has an average of 90%, the average of all 22 kids is not 85%.

 

[Szaboteur]

so, which one should i work with? Every column is a an element of an abstract construct. A competence more exactly. And every row is a participant. i want to know the mean for the whole competence, in the first place, and on the second hand, i want to know the mean for every element of the competence (on the columns).

 

[shg]

The mean for each competency is the column average, the mean for each participant is the row average, and the mean for all competencies is the table average. What's your question?

 

[Szaboteur]

[TABLE="width: 825"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LEADESHIP[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]plans work[/TD]
[TD]set objectives[/TD]
[TD]monitors accomplishment[/TD]
[TD]gives
feedback[/TD]
[TD]motivate[/TD]
[TD] sustain good atmosphere[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]3.83[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]4.17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]3.67[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]5.00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]4.83[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4.17[/TD]
[TD="align: right"]4.29[/TD]
[TD="align: right"]4.25[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4.38[/TD]
[TD="align: right"]4.43[/TD]
[TD="align: right"]3.83
[/TD]
[/TR]
</tbody>[/TABLE]


The competence, lets say, is leadership which is decribed by 6 behaviors. Every participant rate every behavior.
Vertically, i want the mean for every individual behavior scored by all participants from the group.
I thought that, vertically, averaging the averages i will obtain the mean for the whole group for the leadership competency. Horizontally, i thought that i obtained the average for every behavior of the leadership competency, also by the whole group.
My questions is, which average represent the whole leadership competency mean accuratly? vertically or horizontally (4.17 or 4.20?) and second question is, what the other mean does it mean?

 

[shg]

Every participant rate every behavior.

If that's true, then why are zeros ignored?

 

[Szaboteur]

The software gives the value 0 when the participant have chosen <cannot judge><cannot judge"="" <cannot="" judge="">. And the posibility to be no value at all, means that the participant had gave up and closed the the completion. This is why i use this formula =IFERROR(AVERAGEIF(K55:P55,"<>0",K55:P55),""), to make an average even if a have 0 or when i have no value in the cell. For me, No behavior observed means 1. 5 means always observed. </cannot>

 

[Szaboteur]

TYPOS:
The software gives the value 0 when the participant have chosen <cannot judge><cannot judge"="" <cannot="" judge="">. And the posibility to be no value at all, means that the participant had gave up and closed the the completion. This is why i use this formula =IFERROR(AVERAGEIF(K55:P55,"<>0",K55:P55),""), to make an average even if a have 0 or when i have no value in the cell. For me, No behavior observed means 1. 5 means always observed.</cannot>

 

[shg]

4.17 is the average expressed overall leadership competency.

The mean for each expressed sub-competency is the column average, and the mean of the expressed sub-competencies of each participant is the row average.

 

[Szaboteur]

Then is reasonably to work with the averages (red - horizontally) to express the average for every behavior of the leadeship competency scored by the whole group??

[TABLE="width: 620"]
<colgroup><col width="107" style="mso-width-source:userset;mso-width-alt:3913;width:80pt"> <col width="62" style="mso-width-source:userset;mso-width-alt:2267;width:47pt"> <col width="81" style="mso-width-source:userset;mso-width-alt:2962;width:61pt"> <col width="85" style="mso-width-source:userset;mso-width-alt:3108;width:64pt"> <col width="158" style="mso-width-source:userset;mso-width-alt:5778;width:119pt"> <col width="104" style="mso-width-source:userset;mso-width-alt:3803;width:78pt"> <col width="64" style="width:48pt"> <col width="164" style="mso-width-source:userset;mso-width-alt:5997;width:123pt"> </colgroup><tbody>[TR]
[TD="width: 107"][/TD]
[TD="width: 62"][/TD]
[TD="class: xl69, width: 81"]plans work[/TD]
[TD="class: xl69, width: 85"]set objectives[/TD]
[TD="class: xl69, width: 158"]monitors accomplishment[/TD]
[TD="class: xl69, width: 104"]gives feedback[/TD]
[TD="class: xl69, width: 64"]motivate[/TD]
[TD="class: xl69, width: 164"]sustain good atmosphere[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]4.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]3.83[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]4.17[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]3.67[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]4.00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]4.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]5.00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]4.83[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66, align: right"]4.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="class: xl69"]name[/TD]
[TD="class: xl66"] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl68, align: right"]4.17[/TD]
[TD="class: xl67, align: right"]4.29[/TD]
[TD="class: xl66, align: right"]4.25[/TD]
[TD="class: xl66, align: right"]4.00[/TD]
[TD="class: xl66, align: right"]4.38[/TD]
[TD="class: xl66, align: right"]4.43[/TD]
[TD="class: xl66, align: right"]3.83[/TD]
[/TR]
</tbody>[/TABLE]


<body id="************" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">LEADESHIPplans workset objectivesmonitors accomplishmentgives
feedbackmotivatesustain good atmospherename4.00044000name3.83543443name4.17444553name3.67343444name4.00004400name4.00444444name5.00555555name4.83555554name4.00444440name0000004.174.294.254.004.384.433.83
</body>