I have spreadsheet of data that is set up as follows (I haven't populated the whole table with scores but hopefully you get the idea):
<tbody>
</tbody>
The data set is much bigger in real life. I need to find the average scores by area, score type and subject i.e. average if area =a score type = learner and subject = subject 1
I have tried using arrays but got the error "Excel ran out of resources while attmempting to calculate one or more formulas. As a result, these formulas cannot be evaluated".
I have therefore been trying to use sum product but just can't work out how to write the formula. I am also still getting the above error which is making it hard to know if I'm along the right lines with the sumproduct formula (as I can't see if it is calculating correctly). If I can get the formula right I think I can then break the formula down in to bits and put these helper cells to avoid getting the error? Or is there a better way of working out the averages?
Any help would be greatly appreciated
Thanks
Area | Score Type | Subject 1 | Subject 1 | Subject 1 | Subject 2 | Subject 2 | Subject 3 | Subject 3 |
A | Learner | 4.5 | 3 | 2 | 4 | 1 | 5 | |
A | LM | 3.2 | 4 | 2 | 4 | 2.5 | 3 | 1.5 |
A | Average | 3.9 | 3.5 | 2 | 4 | 1.8 | 4 | 0.8 |
B | Learner | |||||||
B | LM | |||||||
B | Average | |||||||
C | Learner | |||||||
C | LM | |||||||
C | Average |
<tbody>
</tbody>
The data set is much bigger in real life. I need to find the average scores by area, score type and subject i.e. average if area =a score type = learner and subject = subject 1
I have tried using arrays but got the error "Excel ran out of resources while attmempting to calculate one or more formulas. As a result, these formulas cannot be evaluated".
I have therefore been trying to use sum product but just can't work out how to write the formula. I am also still getting the above error which is making it hard to know if I'm along the right lines with the sumproduct formula (as I can't see if it is calculating correctly). If I can get the formula right I think I can then break the formula down in to bits and put these helper cells to avoid getting the error? Or is there a better way of working out the averages?
Any help would be greatly appreciated
Thanks