Sum Product to average values with criteria across rows and columns

[CarolynS]

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):


[TABLE="width: 500"]
<tbody>[TR]
[TD]Area
[/TD]
[TD]Score Type
[/TD]
[TD]Subject 1
[/TD]
[TD]Subject 1
[/TD]
[TD]Subject 1
[/TD]
[TD]Subject 2
[/TD]
[TD]Subject 2
[/TD]
[TD]Subject 3
[/TD]
[TD]Subject 3
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Learner
[/TD]
[TD]4.5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]LM
[/TD]
[TD]3.2
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]2.5
[/TD]
[TD]3
[/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Average
[/TD]
[TD]3.9
[/TD]
[TD]3.5
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]1.8
[/TD]
[TD]4
[/TD]
[TD]0.8
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Learner
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]LM
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Average
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]Learner
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]LM
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]Average
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][td="bgcolor: #DCE6F1"]

N​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Area​

[/td][td]

Score Type​

[/td][td]

Subject 1​

[/td][td]

Subject 1​

[/td][td]

Subject 1​

[/td][td]

Subject 2​

[/td][td]

Subject 2​

[/td][td]

Subject 3​

[/td][td]

Subject 3​

[/td][td][/td][td]

Area​

[/td][td]

Score Type​

[/td][td]

Subject​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

A​

[/td][td]

Learner​

[/td][td]

4,5​

[/td][td]

3​

[/td][td]

2​

[/td][td]

4​

[/td][td]

1​

[/td][td]

5​

[/td][td][/td][td][/td][td]

A​

[/td][td]

Learner​

[/td][td]

Subject 1​

[/td][td]

3,167​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

A​

[/td][td]

LM​

[/td][td]

3,2​

[/td][td]

4​

[/td][td]

2​

[/td][td]

4​

[/td][td]

2,5​

[/td][td]

3​

[/td][td]

1,5​

[/td][td][/td][td]

A​

[/td][td]

LM​

[/td][td]

Subject 2​

[/td][td]

3,250​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

A​

[/td][td]

Average​

[/td][td]

3,9​

[/td][td]

3,5​

[/td][td]

2​

[/td][td]

4​

[/td][td]

1,8​

[/td][td]

4​

[/td][td]

0,8​

[/td][td][/td][td]

A​

[/td][td]

Average​

[/td][td]

Subject 3​

[/td][td]

2,400​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

B​

[/td][td]

Learner​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

B​

[/td][td]

LM​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

B​

[/td][td]

Average​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

C​

[/td][td]

Learner​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

C​

[/td][td]

LM​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

C​

[/td][td]

Average​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Criteria in columns K:M

Array formula in N2 copied down
=AVERAGEIF(C$1:I$1,M2,INDEX(C$2:I$10,MATCH(1,IF(A$2:A$10=K2,IF(B$2:B$10=L2,1)),0),0))
Ctrl+Shift+Enter

Hope this helps

M.

 

[CarolynS]

HI Marcelo

Thanks for your response. Sorry for the delay in replying. I had tried to use an array but that was what was causing the error. I can't remember the exact formula I used so will give this one a go and see if there's any change

Thanks

 

[CarolynS]

Hi Marcelo

So, I tried your formula and I'm not getting an errors any more, however I don't seem to be getting the right answers either. The formula I'm using (as an array):

=AVERAGEIF('Diagnostic Raw Data'!$S$1:$BR$1,B$40,INDEX('Diagnostic Raw Data'!$S$1:$BR$6412,MATCH(1,IF('Diagnostic Raw Data'!$P2:$P6414=$A42,IF('Diagnostic Raw Data'!$R$1:$R$6413="Learner Score",1)),0),0))

where:

'Diagnostic Raw Data'!$S$1:$BR$1 is the subject headings in my raw data set

B$40 is the subject heading criteria

'Diagnostic Raw Data'!$S$1:$BR$6412 is the range in my raw data containing the scores I'm trying to calculate

'Diagnostic Raw Data'!$P2:$P6414 is the criteria range in my raw data containing the area name

$A42 is the criteria for the area name

'Diagnostic Raw Data'!$R$1:$R$6413 is the criteria range in my raw data containing the score type

="Learner Score" is obviously the criteria for the score type

My criteria are laid out slightly differently to yours for my calculations. I'm just looking at learner score at the moment and my table is as below (but I can't see that this should cause an issue?). I am getting values populated in it but when I double check the calculations manually, the answers the formula is giving are wrong

Does it look like I've gone wrong somewhere?

Thanks

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Subject 1[/TD]
[TD]Subject 2[/TD]
[TD]Subject 3[/TD]
[TD]Subject 4[/TD]
[/TR]
[TR]
[TD]Area A[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD]Area B[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[/TR]
[TR]
[TD]Area C[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[TD]array formula[/TD]
[/TR]
</tbody>[/TABLE]

 

[CarolynS]

I have found a solution using Sumproduct instead of an array where (using the example in my opening post) the following formula works in N2:

=SUMPRODUCT(C2:I10*(A2:A10=K2)*(C1:I1=M2)*(B2:B10=L2))/SUMPRODUCT((C2:I10<>"")*(A2:A10=K2)*(C1:I1=M2)*(B2:B10=L2))