Average calculation help?

[#1]

Hi,

I'm not sure how to create an expression like so:

On the top left table/chart I get an Average of 92%-Avg([Actual Value]), but this is not working it out as expected.

It should be 91%. Worked out as follows : Per Subject: (Actual Value * Number of Respondants) / Total Number of respondants ie.

(0.95*135)/785 = 91.% (Biological Sciences)

Then doing an average of all of these calculations for the Question...In this case, Question 1.

I've illustrated this in the attached excel sheet.

I basically want to be able to create a Radar chart, with Questions and these averages.

Any ideas appreciated!

[johnw · Accepted solution]

I think you have a spreadsheet error.  You're calculating 9.1062%, not the desired 91.062%.  It's hard to see the error because you have exactly 10 records, so it's only off a decimal place instead of messing up any digits.  I believe you should be doing a sum, not an average.  You've already handled the average (and made it a weighted average) by dividing by 785.

So what I think you want is this:

sum(aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level]))/sum([Number of Respondents])

It produces 91.062%, in any case.