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

 

[offthelip]

try this:
=AVERAGE(OFFSET(B4,0,0,1,COUNTA(B4:AL4)))

 

[PMRetired2012]

try this:
=AVERAGE(OFFSET(B4,0,0,1,COUNTA(B4:AL4)))

That did not work..it isn't including the other two rows.

 

[Fluff]

How about

=AVERAGE(B4:AL4,B6:AL6,B8:AL8)

 

[PMRetired2012]

How about

=AVERAGE(B4:AL4,B6:AL6,B8:AL8)

Fluff that didnt work either. what i think people are forgetting is as the the weeks go accross and data is being put in that you have to average by the number of weeks data is entered. Example: 3 weeks of data would be divided be 3 4 weeks by 4. and also all ranges would have to be added up and then averaged. i belive that is how you would find the average attendance of all 3 ranges.

 

[Fluff]

To me the average is the sum of all numbers, divided by how many numbers there are. If that is not you want, then you need to explain, along with some sample data, showing more than one column (preferably not an image).

Also please update your account details to show which version of Excel you are using.

 

[PMRetired2012]

To me the average is the sum of all numbers, divided by how many numbers there are. If that is not you want, then you need to explain, along with some sample data, showing more than one column (preferably not an image).

Also please update your account details to show which version of Excel you are using.

To me the average is the sum of all numbers, divided by how many numbers there are. If that is not you want, then you need to explain, along with some sample data, showing more than one column (preferably not an image).

Also please update your account details to show which version of Excel you are using

That image has all the data you need !!! Average in this case needs to show a running average total for each week and will be divided by the number of weeks the way i see it. The first week 10-13 average should be 27 because you have only one week to figure on. that is the way i see it.
also how do i add a sheet with sample data on it?
im using excel 365.

 

[Fluff]

The image has one weeks worth of data, so we cannot see what you expect for more than one week & the formula I suggested returns the same number as your image for AE24, which is what you asked for. So no ,That image does not have all the data I need.
You can post sample data to the board with the XL2BB add-in. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[PMRetired2012]

The image has one weeks worth of data, so we cannot see what you expect for more than one week & the formula I suggested returns the same number as your image for AE24, which is what you asked for. So no ,That image does not have all the data I need.
You can post sample data to the board with the XL2BB add-in. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Fluff,. I Think i did this right.
The cell that has the wrong formula is AE24. what i want to happen is the same type formula that is used in cell AH24. If you will notice Cell AH24 only uses 1 row to average. that is (35+24+2) and the = 51 and the answer is based on 3 weeks entries.
What is want to do is use 3 rows totals (all data entries) and divide them by 3 weeks to come up with a average only on the the rows. (Rows: B6:AL6, B8:AL8, B10:AL10)

Thanks

Kids N Christ Count 21-22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL
1	MASTER SHEET	10/13	10/20	10/27	11/3	11/10	11/17	11/24	12/1	12/8	12/15	12/22	12/29	1/5	1/12	1/19	1/26	2/2	2/9	2/16	2/23	3/2	3/9	3/16	3/23	3/30	4/6	4/13	4/20	4/27	5/4	5/11	5/18	5/25	6/1	6/8	6/15	6/22
2
3
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
21
22		MONTHLY CLASS ADVERAGES																											YTD ADVERAGE		KIDS YTD ADVERAGE			TOTAL ADV ATTE
23		OCT			NOV			DEC			JAN			FEB			MAR			APRIL			MAY
24	KIDS	1			0			0			0			0			0			0			0			0			1		9			17
25	7TH THRU 8TH	11			0			0			0			0			0			0			0			0			11
26	9TH THRU 12TH	5			0			0			0			0			0			0			0			0			5
27	ADULT CLASS	3			0			0			0			0			0			0			0			0			3
28	TEACHERS	0			0			0			0			0			0			0			0			0			0
29	WORKERS	0			0			0			0			0			0			0			0			0			0
30	VISITORS	4			0			0			0			0			0			0			0			0			4
31
32
33
34
35
36
37
38
39
40
MASTER SHEET

Cell Formulas
Range	Formula
B1	B1	='BUTTON PAGE'!F34
C1:AL1	C1	=B1+7
B4:AL4	B4	=SUM(NURSERY!B33)
B6:AL6	B6	=SUM('PRE K & K:5TH & 6TH GIRLS'!B33)
B8	B8	=SUM('7TH & 8TH'!B33)
B10:AL10	B10	=SUM('9TH - 12TH'!B33)
B14:AL14	B14	=SUM('NURSERY:9TH - 12TH'!B40)
B16:AL16	B16	=SUM(WORKERS!B40)
B18:AL18	B18	=SUM(VISITORS!B40)
B20:AL20	B20	=SUM(B3:B19)
AE24	AE24	=IF(ISNUMBER(AVERAGEIF(B4:B8,">0")),AVERAGEIF(B4:B8,">0"),"0")
AH24	AH24	=IF(ISNUMBER(AVERAGEIF(B20:AL20,">0")),AVERAGEIF(B20:AL20,">0"),"0")
B24	B24	=IFERROR(AVERAGEIF(B4:E4,">0"),"0")
B25	B25	=IFERROR(AVERAGEIF(B6:E6,">0"),"0")
B26	B26	=IFERROR(AVERAGEIF(B8:E8,">0"),"0")
B27	B27	=IFERROR(AVERAGEIF(B10:D10,">0"),"0")
B28	B28	=IFERROR(AVERAGEIF(B12:E12,">0"),"0")
B29	B29	=IFERROR(AVERAGEIF(B14:E14,">0"),"0")
B30	B30	=IFERROR(AVERAGEIF(B16:E16,">0"),"0")
E24	E24	=IFERROR(AVERAGEIF(F4:I4,">0"),"0")
E25	E25	=IFERROR(AVERAGEIF(F6:I6,">0"),"0")
E26	E26	=IFERROR(AVERAGEIF(F8:I8,">0"),"0")
E27	E27	=IFERROR(AVERAGEIF(F10:I10,">0"),"0")
E28	E28	=IFERROR(AVERAGEIF(F12:I12,">0"),"0")
E29	E29	=IFERROR(AVERAGEIF(F14:I14,">0"),"0")
E30	E30	=IFERROR(AVERAGEIF(F16:I16,">0"),"0")
H24	H24	=IFERROR(AVERAGEIF(J4:N4,">0"),"0")
H25	H25	=IFERROR(AVERAGEIF(J6:N6,">0"),"0")
H26	H26	=IFERROR(AVERAGEIF(J8:N8,">0"),"0")
H27	H27	=IFERROR(AVERAGEIF(J10:N10,">0"),"0")
H28	H28	=IFERROR(AVERAGEIF(J12:N12,">0"),"0")
H29	H29	=IFERROR(AVERAGEIF(J14:N14,">0"),"0")
H30	H30	=IFERROR(AVERAGEIF(J16:N16,">0"),"0")
K24	K24	=IFERROR(AVERAGEIF(O4:R4,">0"),"0")
K25	K25	=IFERROR(AVERAGEIF(O6:R6,">0"),"0")
K26	K26	=IFERROR(AVERAGEIF(O8:R8,">0"),"0")
K27	K27	=IFERROR(AVERAGEIF(O10:R10,">0"),"0")
K28	K28	=IFERROR(AVERAGEIF(O12:R12,">0"),"0")
K29	K29	=IFERROR(AVERAGEIF(O14:R14,">0"),"0")
K30	K30	=IFERROR(AVERAGEIF(O16:R16,">0"),"0")
N24	N24	=IFERROR(AVERAGEIF(S4:V4,">0"),"0")
N25	N25	=IFERROR(AVERAGEIF(S6:V6,">0"),"0")
N26	N26	=IFERROR(AVERAGEIF(S8:V8,">0"),"0")
N27	N27	=IFERROR(AVERAGEIF(S10:V10,">0"),"0")
N28	N28	=IFERROR(AVERAGEIF(S12:V12,">0"),"0")
N29	N29	=IFERROR(AVERAGEIF(S14:V14,">0"),"0")
N30	N30	=IFERROR(AVERAGEIF(S16:V16,">0"),"0")
Q24	Q24	=IFERROR(AVERAGEIF(W4:AA4,">0"),"0")
Q25	Q25	=IFERROR(AVERAGEIF(W6:AA6,">0"),"0")
Q26	Q26	=IFERROR(AVERAGEIF(W8:AA8,">0"),"0")
Q27	Q27	=IFERROR(AVERAGEIF(W10:AA10,">0"),"0")
Q28	Q28	=IFERROR(AVERAGEIF(W12:AA12,">0"),"0")
Q29	Q29	=IFERROR(AVERAGEIF(W14:AA14,">0"),"0")
Q30,T30	Q30	=IFERROR(AVERAGEIF(X16:AA16,">0"),"0")
T24	T24	=IFERROR(AVERAGEIF(AB4:AE4,">0"),"0")
T25	T25	=IFERROR(AVERAGEIF(AB6:AE6,">0"),"0")
T26	T26	=IFERROR(AVERAGEIF(AB8:AE8,">0"),"0")
T27	T27	=IFERROR(AVERAGEIF(AB10:AE10,">0"),"0")
T28	T28	=IFERROR(AVERAGEIF(AB12:AE12,">0"),"0")
T29	T29	=IFERROR(AVERAGEIF(AB14:AE14,">0"),"0")
W24	W24	=IFERROR(AVERAGEIF(AF4:AI4,">0"),"0")
W25	W25	=IFERROR(AVERAGEIF(AF6:AI6,">0"),"0")
W26	W26	=IFERROR(AVERAGEIF(AF8:AI8,">0"),"0")
W27	W27	=IFERROR(AVERAGEIF(AF10:AI10,">0"),"0")
W28	W28	=IFERROR(AVERAGEIF(AF12:AI12,">0"),"0")
W29	W29	=IFERROR(AVERAGEIF(AF14:AI14,">0"),"0")
W30	W30	=IFERROR(AVERAGEIF(AF16:AI16,">0"),"0")
Z24	Z24	=IFERROR(AVERAGEIF(AJ4:AL4,">0"),"0")
Z25	Z25	=IFERROR(AVERAGEIF(AJ6:AL6,">0"),"0")
Z26	Z26	=IFERROR(AVERAGEIF(AJ8:AL8,">0"),"0")
Z27	Z27	=IFERROR(AVERAGEIF(AJ10:AL10,">0"),"0")
Z28	Z28	=IFERROR(AVERAGEIF(AJ12:AL12,">0"),"0")
Z29:Z30	Z29	=IFERROR(AVERAGEIF(AJ14:AL14,">0"),"0")
AC24	AC24	=IF(ISNUMBER(AVERAGEIF(B4:AL4,">0")),AVERAGEIF(B4:AL4,">0"),"0")
AC25	AC25	=IF(ISNUMBER(AVERAGEIF(B6:AL6,">0")),AVERAGEIF(B6:AL6,">0"),"0")
AC26	AC26	=IF(ISNUMBER(AVERAGEIF(B8:AL8,">0")),AVERAGEIF(B8:AL8,">0"),"0")
AC27	AC27	=IF(ISNUMBER(AVERAGEIF(B10:AL10,">0")),AVERAGEIF(B10:AL10,">0"),"0")
AC28	AC28	=IF(ISNUMBER(AVERAGEIF(B12:AL12,">0")),AVERAGEIF(B12:AL12,">0"),"0")
AC29	AC29	=IF(ISNUMBER(AVERAGEIF(B14:AL14,">0")),AVERAGEIF(B14:AL14,">0"),"0")
AC30	AC30	=IF(ISNUMBER(AVERAGEIF(B16:AL16,">0")),AVERAGEIF(B16:AL16,">0"),"0")

 

[Fluff]

Thanks for that, from your original image we had no way of knowing the the "blank" cells actually contain a 0.
Will the odd numbered rows be empty, or do they have something in them?