Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.