Average the last three values within a range if the day of week is MON

[StormFusion]

Hi,

I have values in range N12:N98 and the day of week in D12:D98 how do I get the average of the very last three values within range N12:N98 if the day is MON and the value is greater than 0

Thanks

 

[StephenCrump]

Do you mean like this?

	A	B	C	D
9	Day	Value
10	Sat 1 Jan 2022	7	Last 3 positive	7
11	Sun 2 Jan 2022	-5
12	Mon 3 Jan 2022	-8
13	Tue 4 Jan 2022	-10
14	Sat 8 Jan 2022	0
15	Sun 9 Jan 2022	-10
16	Mon 10 Jan 2022	-5
17	Tue 11 Jan 2022	-10
18	Sun 16 Jan 2022	7
19	Mon 17 Jan 2022	6
20	Tue 18 Jan 2022	-4
21	Fri 21 Jan 2022	0
22	Sat 22 Jan 2022	-9
23	Sun 23 Jan 2022	-2
24	Mon 24 Jan 2022	1
25	Thu 27 Jan 2022	0
26	Fri 28 Jan 2022	2
27	Sat 29 Jan 2022	5
28	Sun 30 Jan 2022	-4
29	Mon 31 Jan 2022	2
30	Wed 2 Feb 2022	-4
31	Sat 5 Feb 2022	2
32	Mon 7 Feb 2022	-1
33	Tue 8 Feb 2022	-4
34	Wed 9 Feb 2022	8
35	Thu 10 Feb 2022	5
36	Fri 11 Feb 2022	-1
37	Sun 13 Feb 2022	-8
38	Mon 14 Feb 2022	3
39	Wed 16 Feb 2022	2
40	Thu 17 Feb 2022	-3
41	Sat 19 Feb 2022	-10
42	Sun 20 Feb 2022	6
43	Mon 21 Feb 2022	-6
44	Wed 23 Feb 2022	-7
45	Thu 24 Feb 2022	8
46	Sun 27 Feb 2022	4
47	Mon 28 Feb 2022	7
48	Tue 1 Mar 2022	-4
Sheet1

Cell Formulas
Range	Formula
D10	D10	=SUM(INDEX(B10:B48,LARGE(IF((WEEKDAY(A10:A48)=2)*(B10:B48>0),ROW(A10:A48)-ROW(A10)+1),{1,2,3})))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[StormFusion]

Hi thank for that, however how to make it just check the three last Mondays being 28th, 21st and 14th so would ignore the - 6 but average the 7 and 3 thanks

 

[StephenCrump]

What version of Excel are you running?

It would be helpful if you could add that to your signature (you'll see I'm using 365) as the best solution for you may vary depending on your Excel version.

 

[StormFusion]

Not totally sure as not near it at moment, but it's 2019 I think non 365 edition

 

[StephenCrump]

OK, try:

	A	B	C	D
1	Day	Value
2	Sat 1 Jan 2022	7	Last 3 Mon's	5
3	Sun 2 Jan 2022	-5	(if pos)
4	Mon 3 Jan 2022	-8
5	Tue 4 Jan 2022	-10
6	Sat 8 Jan 2022	0
7	Sun 9 Jan 2022	-10
8	Mon 10 Jan 2022	-5
9	Tue 11 Jan 2022	-10
10	Sun 16 Jan 2022	7
11	Mon 17 Jan 2022	6
12	Tue 18 Jan 2022	-4
13	Fri 21 Jan 2022	0
14	Sat 22 Jan 2022	-9
15	Sun 23 Jan 2022	-2
16	Mon 24 Jan 2022	1
17	Thu 27 Jan 2022	0
18	Fri 28 Jan 2022	2
19	Sat 29 Jan 2022	5
20	Sun 30 Jan 2022	-4
21	Mon 31 Jan 2022	2
22	Wed 2 Feb 2022	-4
23	Sat 5 Feb 2022	2
24	Mon 7 Feb 2022	-1
25	Tue 8 Feb 2022	-4
26	Wed 9 Feb 2022	8
27	Thu 10 Feb 2022	5
28	Fri 11 Feb 2022	-1
29	Sun 13 Feb 2022	-8
30	Mon 14 Feb 2022	3
31	Wed 16 Feb 2022	2
32	Thu 17 Feb 2022	-3
33	Sat 19 Feb 2022	-10
34	Sun 20 Feb 2022	6
35	Mon 21 Feb 2022	-6
36	Wed 23 Feb 2022	-7
37	Thu 24 Feb 2022	8
38	Sun 27 Feb 2022	4
39	Mon 28 Feb 2022	7
40	Tue 1 Mar 2022	-4
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IFERROR(AVERAGE(IF((ROW(B2:B40)>=LARGE(IF((WEEKDAY(A2:A40)=2),ROW(A2:A40)-ROW(A2)+1),3))*(WEEKDAY(A2:A40)=2)*(B2:B40>0),B2:B40)),"n/a")

 

[Peter_SSs]

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