Average Formulas with 3 ranges to average as data is added weekly

[PMRetired2012]

What I need to do is have a Formula in the cell that says "Kids YTD adverag" that will show the average of ranges B4:AL4 and B6:AL6 and B8:AL8 as data is placed in the cells for each week.
Another words what is want is as we move thru the dates (10-3 thru 6-22) we get a adverage YTD for all those ranges. The answer will show up in the Kids YTD average or Cell AE24.

Thanks

 

[PMRetired2012]

LOL All i got was TRUE...i dont want anyting in B7

Fluff can you give me anymore ideas on how to get this to work without getting the #div/0 error?

 

[ravi2628]

Hi can you try this once

looks a bit hard way but I think logic works

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	MASTER SHEET	10/13	10/20	10/27	11/03	11/10	11/17	11/24	12/01	12/08	12/15
2
3	Month	Oct	Oct	Oct	Nov	Nov	Nov	Nov	Dec	Dec	Dec
4	NURSERY	1	4	7	8	5	4	1	7	10	5
5
6	KIDS (PREK - 6TH)	20	11	2
7
8	7TH THRU 8TH	5	4	3	6	2	10	1	1	1	3
9
10	9TH THRU 12TH	3	3
11
12	ADULT CLASS	7	6	8	6	8	10	9	2	9
13
14	TEACHERS
15
16	WORKERS	4	9	2	4	9	4	1	1	9	8
17
18	VISITORS	2	10	2	2	5	8	10	3	5	7
19
20	GRAND TOTAL	42	47	24	26	29	36	22	14	34	23
21
22
23		Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	YTD Adverage	KIDS Adverage	Total Adverage
24	NURSERY	4	5	7						5	7	30
25	KIDS (PREK - 6TH)	11								11
26	7TH THRU 8TH	4	5	2						3
27	9TH THRU 12TH	3								3
28	ADULT CLASS	7	8	6						7
29	TEACHERS
30	WORKERS	5	5	6						5
31	VISITORS	5	6	5						5
Sheet1

Cell Formulas
Range	Formula
C1:K1	C1	=B1+7
B3:K3	B3	=TEXT(B1,"MMM")
B20:K20	B20	=SUM(B4:B19)
B24:I24	B24	=IFERROR(AVERAGEIFS($B$4:$XFD$4,$B$3:$XFD$3,B$23),"")
J24:J31	J24	=IFERROR(AVERAGE(B24:I24),"")
K24	K24	=AVERAGE(4:4,6:6)
L24	L24	=AVERAGE(20:20)
B25:I25	B25	=IFERROR(AVERAGEIFS($B$6:$XFD$6,$B$3:$XFD$3,B$23),"")
B26:I26	B26	=IFERROR(AVERAGEIFS($B$8:$XFD$8,$B$3:$XFD$3,B$23),"")
B27:I27	B27	=IFERROR(AVERAGEIFS($B$10:$XFD$10,$B$3:$XFD$3,B$23),"")
B28:I28	B28	=IFERROR(AVERAGEIFS($B$12:$XFD$12,$B$3:$XFD$3,B$23),"")
B29:I29	B29	=IFERROR(AVERAGEIFS($B$14:$XFD$14,$B$3:$XFD$3,B$23),"")
B30:I30	B30	=IFERROR(AVERAGEIFS($B$16:$XFD$16,$B$3:$XFD$3,B$23),"")
B31:I31	B31	=IFERROR(AVERAGEIFS($B$18:$XFD$18,$B$3:$XFD$3,B$23),"")

 

[ravi2628]

Hi,

Have you tried this formula,

AVERAGE(FILTER(4:8,3:3<>"",0))

Adverages.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	MASTER SHEET	10/13	10/20	10/27	11/03	11/10	11/17
2
3	Month	Oct	Oct	Oct	Nov	Nov	Nov
4	NURSERY	1
5
6	KIDS (PREK - 6TH)	20	11	2
7
8	7TH THRU 8TH	5
9
10	9TH THRU 12TH	3	3
11
12	ADULT CLASS
13
14	TEACHERS
15
16	WORKERS	4
17
18	VISITORS	2
19
20	GRAND TOTAL	35	14	2	0	0	0	0	0	0	0
21
22
23		Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	YTD Adverage	KIDS Adverage	Total Adverage
24	NURSERY	1								1	8	5
25	KIDS (PREK - 6TH)	11								11
26	7TH THRU 8TH	5								5
27	9TH THRU 12TH	3								3
28	ADULT CLASS
29	TEACHERS
30	WORKERS	4								4
31	VISITORS	2								2
Sheet1

Cell Formulas
Range	Formula
C1:G1	C1	=B1+7
B3:K3	B3	=IF(B1="","",TEXT(B1,"MMM"))
B20:K20	B20	=SUM(B4:B19)
B24:I24	B24	=IFERROR(AVERAGEIFS($B$4:$XFD$4,$B$3:$XFD$3,B$23),"")
J24:J31	J24	=IFERROR(AVERAGE(B24:I24),"")
K24	K24	=AVERAGE(FILTER(4:8,3:3<>"",0))
L24	L24	=AVERAGE(20:20)
B25:I25	B25	=IFERROR(AVERAGEIFS($B$6:$XFD$6,$B$3:$XFD$3,B$23),"")
B26:I26	B26	=IFERROR(AVERAGEIFS($B$8:$XFD$8,$B$3:$XFD$3,B$23),"")
B27:I27	B27	=IFERROR(AVERAGEIFS($B$10:$XFD$10,$B$3:$XFD$3,B$23),"")
B28:I28	B28	=IFERROR(AVERAGEIFS($B$12:$XFD$12,$B$3:$XFD$3,B$23),"")
B29:I29	B29	=IFERROR(AVERAGEIFS($B$14:$XFD$14,$B$3:$XFD$3,B$23),"")
B30:I30	B30	=IFERROR(AVERAGEIFS($B$16:$XFD$16,$B$3:$XFD$3,B$23),"")
B31:I31	B31	=IFERROR(AVERAGEIFS($B$18:$XFD$18,$B$3:$XFD$3,B$23),"")

 

[Fluff]

LOL All i got was TRUE...i dont want anyting in B7

That's fine, it was just to check the value. Is the formula in post#15 showing the correct answer?