How to get the central tendency?

[tosime]

Background
A group of trainees have each completed a training evaluation form.
We have collated the results in an Excel table.

The table header represents the degree to which they want more or less of an item.
This ranges from -5 (much less) to 0 (keep it as it is) to +5 (much more).

The Y axis represents the items they were assessing.

Each number within the table represents the number of ticks for that item/assessment.

The Challenge
I want to generate a central tendency for each row.
That is, what value (one number), in what position (between -5, to 0, to +5, to 1 decimal place) best represents that row of data?

The Table
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 116"][/TD]
[TD="width: 19, align: center"]-5[/TD]
[TD="width: 19, align: center"]-4[/TD]
[TD="width: 19, align: center"]-3[/TD]
[TD="width: 19, align: center"]-2[/TD]
[TD="width: 19, align: center"]-1[/TD]
[TD="width: 21, align: center"]0[/TD]
[TD="width: 21, align: center"]+1[/TD]
[TD="width: 21, align: center"]+2[/TD]
[TD="width: 21, align: center"]+3[/TD]
[TD="width: 21, align: center"]+4[/TD]
[TD="width: 21, align: center"]+5[/TD]
[/TR]
[TR]
[TD="align: right"]Topics[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]Humor[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]Theory[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]Faster Delivery[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]Processes[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]Trainee Activity[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]Depth in Material[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]Examples (Facts)[/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"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]Audio/video[/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"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]Past Perspective[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]Statements[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]Class Freedom[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

The Result should look like this:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="align: center"]Item
[/TD]
[TD="align: center"]Value
[/TD]
[TD="align: center"]Position
[/TD]
[/TR]
[TR]
[TD]Topics[/TD]
[TD]4.4[/TD]
[TD]+1.3[/TD]
[/TR]
[TR]
[TD]Humor[/TD]
[TD]1.5[/TD]
[TD]-2.1[/TD]
[/TR]
</tbody>[/TABLE]
(These are hypothetical)

Notes
I want to be able to rank the items in terms of importance, taking into account both the value and the position.
The higher the value, the more important it is.
The further the position from zero, the more important it is.

PS. Is there a better way to get this result?
What branch of maths can I study to learn more about this type of problem?

I look forward to your reply - and thanks a million in advance!

 

[palaeontology]

Re: This is a tough one: How to get the central tendency?

You'd be aware there are three ways to find the central tendency of data ... mean, median and mode.

The sort of data you have is called discrete data, where only certain values are available to enter (eg: you can't enter a score of -3.2).

Discrete Data is usually measured using Median, so assuming your data sits in the range A1:L13, then in N2 enter the following ...

=SMALL($B$1:$L$1,MATCH((SUM(B2:L2)+1)/2,O2:Y2,1)+1)

and copy this down to N13

In cell O2 use this formula ...

=SUM($B2:B2)

and copy it across to Y2 and then down to Y13

If you know how to hide columns O to Y, then do so, as you don't need to see these columns anymore

The values in the range N2:N13 are your median scores for each category.


As for your 'Position' value, I'm not sure what you really want there ... can you explain that one a little further ?

Kind regards,

Chris

 

[tosime]

Re: This is a tough one: How to get the central tendency?

Hi Chris,

Thank you for your response.

I followed your instructions and got a #NUM! error on N2 to N13.
I could not work out the cause of the error. Could you please explain your formula.

I assume you are arranging the values on each row then selecting the middle value and showing the corresponding X-Axis value between -5 to 0 to +5.
Let me illustrate this with the 'Class Freedom' row.

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]+1[/TD]
[TD="align: center"]+2[/TD]
[TD="align: center"]+3[/TD]
[TD="align: center"]+4[/TD]
[TD="align: center"]+5[/TD]
[/TR]
[TR]
[TD]Class Freedom[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

To get the median, we would arrange the values of "Class Freedom" in order like this:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"]-4[/TD]
[TD="align: center"]-3[/TD]
[TD="align: center"]-2[/TD]
[TD="align: center"]+5[/TD]
[TD="align: center"]+4[/TD]
[TD="align: center"]+3[/TD]
[TD="align: center"]+1
[/TD]
[TD="align: center"]-1
[/TD]
[TD="align: center"]+2[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]Class Freedom[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]
[/TR]
</tbody>[/TABLE]

The median value here would be 2.
It has a corresponding X-Axis of +1 (however, 2 also corresponds to +5, +4, +3).

I feel the 2 is too low a value to represent the desire of the Trainees to keep 'Class Freedom' at its current level.
I feel the value should be closer to the mean of 3.4.

The position value represents how much Trainees want more of something, or less of something, or no change.
If I look at Class Freedom, then most Trainees want to keep it at the current level.

If I were to use my "eye" to subjectively assign a value (central tendency) to class freedom, I would say this:
Class Freedom has "strength" of about 7 and a position of about +1.
While most Trainees want class freedom to stay the same, if we increased it a little, the whole class would be happier.

Compare this to the "Examples" row.
If I were to use my "eye" to subjectively assign a value (central tendency) to Examples, I would say this:
Examples has a strength of about 9 and a position of about +4.
If we included many more examples the class would be much happier.

Also, the class would be happier with more examples compared to more class freedom.
So more examples should have priority over more class freedom.

I hope my comments and examples have helped you better understand my challenge.
I feel this is more like an optimization challenge. Based on the survey results;
what set of actions will lead to the greatest increase in class satisfaction?
Just by looking at the data, I would say:
1) Increase facts -strong
2) Add more videos -strong
3) Include more examples from the past -moderate
4) Increase the depth of the material - moderate
and so on...till x) Reduce humor - slight

I look forward to your response.