Weighted average - irregular dataset

[cacos]

Hi everyone, hoping you can help come up with a solution for the attached file: each row represents a respondent, and they were each given a bucket from 0 to 100 points to allocate to a number of sports activities.

There are 9 possible activities, but not all respondents had the option to see all 9: some, like those in the US, didn't see the option for "Running" for example. And the same happens with "Swimming", there were some respondents that couldn't choose that option to allocate.

What would be the best way to represent, in percentages (something like a weighted average I'd think) what's the overall weight assigned to each sport on that 0-100 scale. The tricky part comes when considering that not all could rate all 9 sport activities.

Hope that was clear.
Thank you!

 

[Nebu]

Hi:

May be something like this.

Thanks

 

[r1c1]

Try something like this:

1. Calculate the total of each sport scores
2. Divide this with the number of people selecting for that sport (ie average)
3. Now, divide this average with total of all averages of all sports.
4. You get % representation.

Same as Nebu's solution.

 

[cacos]

Perfect, thank you both! (what an honor )

I was trying to approach this the "most correct" way in terms of the math behind it; is there any explanation you can think of why this would be "valid" in terms of representing the percentages like this?

 

[Nebu]

Hi:

Since I played around a bit, here another way of representing it.Anyhow its up to you in what way you want to read the data and represent.

Thanks