Average based on criteria

[cunningAce]

Hi,

I am looking for a way to calculate an average total dependant on the selections made.
So in this example the selections are 'Code' = A1 and 'Weeks Avg' = 4
The answer should be 29.66 as I want the formula to average the last 4 weeks figures for anything with code A1.

[TABLE="width: 468"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][TABLE="width: 468"]
<colgroup><col><col span="8"></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Wk 1[/TD]
[TD]Wk 2[/TD]
[TD]Wk 3[/TD]
[TD]Wk 4[/TD]
[TD]Wk 5[/TD]
[TD]Wk 6[/TD]
[TD]Wk 7[/TD]
[TD]Wk 8[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD] A1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Weeks Avg[/TD]
[TD] 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Answer[/TD]
[TD]29.66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Thanks in advance[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

You already got an answer to get the SUM of your range with criteria in this post here: https://www.mrexcel.com/forum/excel-questions/1046453-sum-if-based-selection.html
The AVERAGE is the SUM divided by the COUNT. So now you just need to count the number of records your just summed.
You can get that easily using the exact same formula given to you in the first thread, just remove the part that SUMs the range (the first argument).

So that would look like this (in that original example):

=SUMPRODUCT(($B$1:$E$1=$H$1)*($A$2:$A$6=G2))

So, just divide that first formula by this formula to get your average, i.e.

=SUMPRODUCT(($B$2:$E$6),($B$1:$E$1=$H$1)*($A$2:$A$6=G2))/SUMPRODUCT(($B$1:$E$1=$H$1)*($A$2:$A$6=G2))

 

[cunningAce]

Thanks for the reply but that's not what I am trying to achieve,
I want to specify a 'Code' such as A1 and a 'Wks.' Average', such as 4 and the formula return the average. So the example above would look at the figures in Wk5, Wk6, Wk7 & Wk8 and take the average for all code A1's

Does this make sense?

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

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

1​

[/td][td]Code[/td][td]Wk 1[/td][td]Wk 2[/td][td]Wk 3[/td][td]Wk 4[/td][td]Wk 5[/td][td]Wk 6[/td][td]Wk 7[/td][td]Wk 8[/td][/tr]
[tr][td]

2​

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

1​

[/td][td]

7​

[/td][td]

13​

[/td][td]

19​

[/td][td]

25​

[/td][td]

31​

[/td][td]

37​

[/td][td]

43​

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

3​

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

2​

[/td][td]

8​

[/td][td]

14​

[/td][td]

20​

[/td][td]

26​

[/td][td]

32​

[/td][td]

38​

[/td][td]

44​

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

4​

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

3​

[/td][td]

9​

[/td][td]

15​

[/td][td]

21​

[/td][td]

27​

[/td][td]

33​

[/td][td]

39​

[/td][td]

45​

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

5​

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

4​

[/td][td]

10​

[/td][td]

16​

[/td][td]

22​

[/td][td]

28​

[/td][td]

34​

[/td][td]

40​

[/td][td]

46​

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

6​

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

5​

[/td][td]

11​

[/td][td]

17​

[/td][td]

23​

[/td][td]

29​

[/td][td]

35​

[/td][td]

41​

[/td][td]

47​

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

7​

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

6​

[/td][td]

12​

[/td][td]

18​

[/td][td]

24​

[/td][td]

30​

[/td][td]

36​

[/td][td]

42​

[/td][td]

48​

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

8​

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

9​

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

10​

[/td][td]Code[/td][td]A1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]Weeks Avg[/td][td]

4​

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

12​

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

35.66667​

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

In B12 control+shift+enter, not just enter:

=AVERAGE(IF($A$2:$A$7=B10,OFFSET(INDEX($B$2:$I$2,COLUMNS($B$1:$I$1)-B11+1),0,0,ROWS($B$2:$I$7),4)))

 

[cunningAce]

Thanks Aladin,
This currently only works for the Weeks Avg as '4'.
How can I modify to accept any number and calculate the average?

 

[Aladin Akyurek]

Thanks Aladin,
This currently only works for the Weeks Avg as '4'.
How can I modify to accept any number and calculate the average?

Change the value in B11. Note that I took the value in B11 to mean as the number of columns from left to right.

 

[cunningAce]

Perfect thanks

 

[Aladin Akyurek]

Perfect thanks

You are welcome.