AverageIF, last 4 values

[ilcaa]

hello, i have a column of days of week, mon-sat and values next to them. i want to do a running average of each day (monday, etc) but want to change how many Mondays to use

so if a dynamic cell i will have a value like 3 or 4 or 5 etc.

and the formula will get the average of the "Last 3 Mondays" or "Last 4 Mondays" etc. And to further annoy you, the dates are not in exact sequential order, some weeks the Monday is missing. so formula needs to be able to "find" the last n 'Mondays' regardless of how many rows back they are. thanks if you can assist

81​	Saturday
4​	Monday
94​	Tuesday
161​	Wednesday
143​	Thursday
155​	Friday
145​	Saturday
7​	Monday
88​	Tuesday
133​	Wednesday
140​	Thursday
144​	Friday
146​	Saturday
16​	Monday
16​	Tuesday
188​	Wednesday
148​	Thursday
145​	Friday
135​	Saturday
9​	Monday
118​	Tuesday
160​	Wednesday
142​	Thursday
156​	Friday
133​	Saturday
7​	Monday
84​	Tuesday
120​	Wednesday
160​	Thursday
200​	Friday
170​	Saturday
5​	Monday
122​	Tuesday
138​	Wednesday
128​	Thursday
169​	Friday
147​	Saturday
57​	Monday

 

[JamesCanale]

If you have 365, this will help:

MrExcelPlayground12.xlsx
	A	B	C	D	E
1	3
2	81	Saturday		Monday	23.00
3	4	Monday		Tuesday	108.00
4	94	Tuesday		Wednesday	139.33
5	161	Wednesday		Thursday	143.33
6	143	Thursday		Friday	175.00
7	155	Friday		Saturday	150.00
8	145	Saturday
9	7	Monday
10	88	Tuesday
11	133	Wednesday
12	140	Thursday
13	144	Friday
14	146	Saturday
15	16	Monday
16	16	Tuesday
17	188	Wednesday
18	148	Thursday
19	145	Friday
20	135	Saturday
21	9	Monday
22	118	Tuesday
23	160	Wednesday
24	142	Thursday
25	156	Friday
26	133	Saturday
27	7	Monday
28	84	Tuesday
29	120	Wednesday
30	160	Thursday
31	200	Friday
32	170	Saturday
33	5	Monday
34	122	Tuesday
35	138	Wednesday
36	128	Thursday
37	169	Friday
38	147	Saturday
39	57	Monday
Sheet4

Cell Formulas
Range	Formula
E2:E7	E2	=AVERAGE(INDEX(FILTER($A$2:$A$39,$B$2:$B$39=D2),SEQUENCE($A$1,1,ROWS(FILTER($A$2:$A$39,$B$2:$B$39=D2))-$A$1+1)))

 

[ilcaa]

thanks. (I hate Excel formulas!) how can i open the Excel Playground and play with it... i dont see any buttons or links... thanks!

 

[JamesCanale]

Thats just my file that I use to work out the solutions to problems. the tool to post mini-spreadsheets xl2bb, puts the filename on top.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ilcaa]

is there a way i can "see" the values this formula is using to do the average? thanks

 

[JamesCanale]

sure. Delete the "AVERAGE" in the formula. It will show an array of numbers. Most of them will be a SPILL error.

So don't delete the AVERAGE, replace it with "TRANSPOSE". That will do the trick.

MrExcelPlayground12.xlsx
	A	B	C	D	E	F	G
1	3
2	81	Saturday		Monday	23.00
3	4	Monday		Tuesday	108.00
4	94	Tuesday		Wednesday	139.33
5	161	Wednesday		Thursday	143.33
6	143	Thursday		Friday	175.00
7	155	Friday		Saturday	150.00
8	145	Saturday
9	7	Monday
10	88	Tuesday		Monday	7	5	57
11	133	Wednesday		Tuesday	118	84	122
12	140	Thursday		Wednesday	160	120	138
13	144	Friday		Thursday	142	160	128
14	146	Saturday		Friday	156	200	169
15	16	Monday		Saturday	133	170	147
16	16	Tuesday
17	188	Wednesday
18	148	Thursday
19	145	Friday
20	135	Saturday
21	9	Monday
22	118	Tuesday
23	160	Wednesday
24	142	Thursday
25	156	Friday
26	133	Saturday
27	7	Monday
28	84	Tuesday
29	120	Wednesday
30	160	Thursday
31	200	Friday
32	170	Saturday
33	5	Monday
34	122	Tuesday
35	138	Wednesday
36	128	Thursday
37	169	Friday
38	147	Saturday
39	57	Monday
Sheet4

Cell Formulas
Range	Formula
E2:E7	E2	=AVERAGE(INDEX(FILTER($A$2:$A$39,$B$2:$B$39=D2),SEQUENCE($A$1,1,ROWS(FILTER($A$2:$A$39,$B$2:$B$39=D2))-$A$1+1)))
E10:G15	E10	=TRANSPOSE(INDEX(FILTER($A$2:$A$39,$B$2:$B$39=D10),SEQUENCE($A$1,1,ROWS(FILTER($A$2:$A$39,$B$2:$B$39=D10))-$A$1+1)))
Dynamic array formulas.