Hi - Firstly, I want to say a big thanks to anyone that can help with the following and please bear with me while I explain.
I have a scale of data - lets say the following:
A B C D E
1 1 1 1 1
where the 1's can be replaced by any figure by the person completing the survey.
In the following columns - F G H I J - I have a cumulative total where the following applies:
F = A*0
G = B*1
H = C*2
I= D*3
J= E*4 (expert rating)
There are also 6 rows of this repeated - so essentially it is a matrix: of Rows 1-6, and columns F-J with the cumulative totals I've described. Something like this:
F G H I J
1 0 1 2 3 4
2 0 1 2 3 4 The figures in the matrix can change depending
3 0 1 2 3 4 on what someone puts in base data columns A-E.
4 0 1 2 3 4
5 0 1 2 3 4
6 0 1 2 3 4
I need to find a way of calculating the following:
A score (in percentage terms) - whereby no matter how high any of the relevant stats go each row can only contribute up to a maximum amount (say 16.7%) i.e - if the figure in J1 is 100 - and everywhere else is 0 - this may mean the total value = 16.7%.
I think this will involve calculating a weighted average of each row (1-6) and then aggregating them in the final score.
My initial thought was simply to have a column where I SUM each row (say SUM(A6:E6)) and multiply by the maximum points 4 (so SUM(A6:E6)*4). To make my maximum. And then have another column where I Sum row F6:J6 and divide by the first figure to make my average. Unfortunately, this breaks down because if I increase the number of Gs or Hs - this reduces my overall percentage.
Any assistance is much appreciated - thanks so much for reading this!
I have a scale of data - lets say the following:
A B C D E
1 1 1 1 1
where the 1's can be replaced by any figure by the person completing the survey.
In the following columns - F G H I J - I have a cumulative total where the following applies:
F = A*0
G = B*1
H = C*2
I= D*3
J= E*4 (expert rating)
There are also 6 rows of this repeated - so essentially it is a matrix: of Rows 1-6, and columns F-J with the cumulative totals I've described. Something like this:
F G H I J
1 0 1 2 3 4
2 0 1 2 3 4 The figures in the matrix can change depending
3 0 1 2 3 4 on what someone puts in base data columns A-E.
4 0 1 2 3 4
5 0 1 2 3 4
6 0 1 2 3 4
I need to find a way of calculating the following:
A score (in percentage terms) - whereby no matter how high any of the relevant stats go each row can only contribute up to a maximum amount (say 16.7%) i.e - if the figure in J1 is 100 - and everywhere else is 0 - this may mean the total value = 16.7%.
I think this will involve calculating a weighted average of each row (1-6) and then aggregating them in the final score.
My initial thought was simply to have a column where I SUM each row (say SUM(A6:E6)) and multiply by the maximum points 4 (so SUM(A6:E6)*4). To make my maximum. And then have another column where I Sum row F6:J6 and divide by the first figure to make my average. Unfortunately, this breaks down because if I increase the number of Gs or Hs - this reduces my overall percentage.
Any assistance is much appreciated - thanks so much for reading this!
