6 Replies Latest reply on Feb 11, 2012 8:12 AM by Shawn Wallwork

Weighted Average calculation

I need help with a table calculation involving weighted average.

I am dealing with a survey which has a Rank Order question - respondents are asked to rank order 3 choices that they have on a scale of 1,2,3 where 1 is best and 3 is worst; no duplicates allowed.

I have abstracted the problem into a fruit sampling problem - Tasters are asked to rank Apples, Oranges and Pears on a scale of 1-3. A Rank1 has a weight of 3, Rank2 has a weight of 2 and Rank3 has a weight of 1.

I’ve packaged the problem into an Excel Workbook containing the cross-tab, reshaped data and the manually calculated weighted average.

The packaged workbook contains the first viz.

Hope someone can help create the weighted average viz.

Thanks.

Jaya

• 1. Re: Weighted Average calculation

I'm on Tab 7, so can't provide the workbook for you (assuming you can't upgrade), but here we go:

One case calculation to apply the weight (called Weighted Rank below):

case [Rank]

when 1 then 3

when 2 then 2

when 3 then 1

end

Quick table calculation on the viz to show percent of total:

1 of 1 people found this helpful
• 2. Re: Weighted Average calculation

Jaya, here's Alex's solution in 6.1

• 3. Re: Weighted Average calculation

Thanks.

This gets me started.

By the way, I've upgraded to 7.

• 4. Re: Weighted Average calculation

Shawn,

This is pretty close to the right answer, but it needs one more step and I don't know how to do that.

In my Excel workbook, I show the manually computed weighted average in Sheet3 - Weighted Average.

Apples : 40%

Oranges : 30%

Pears : 30%

Thanks.

Jaya

• 5. Re: Weighted Average calculation

Shawn,

The answer was correct - I turned Stack Marks and that got me the answer.

Thanks guys.

Jaya

• 6. Re: Weighted Average calculation

Jaya, glad to hear it! (It was Alex's correct answer, I just happen to still have 6.1 loaded.)