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]

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?

Odd

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?

They will be totally empty

 

[PMRetired2012]

Odd

They will be totally empty

Any Answers for the problem Fluff?

 

[Fluff]

Ok, as there empty, how about

=AVERAGEIF(B6:AL10,">0")

 

[PMRetired2012]

Ok, as there empty, how about

=AVERAGEIF(B6:AL10,">0")

that is giving me a( #div/0!) error message..it has to be a formula that some how doesnt include odd rows

 

[Fluff]

As long as the odd numbered rows do not contain any numbers, then that formula should work

+Fluff 1.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
1	MASTER SHEET	13-Oct	20-Oct	27-Oct	03-Nov	10-Nov	17-Nov	24-Nov	01-Dec	08-Dec	15-Dec	22-Dec	29-Dec	05-Jan	12-Jan	19-Jan	26-Jan	02-Feb	09-Feb	16-Feb	23-Feb	02-Mar	09-Mar	16-Mar	23-Mar	30-Mar	06-Apr	13-Apr	20-Apr	27-Apr	04-May	11-May	18-May
2
3
4	NURSERY	1
5
6	KIDS (PREK - 6TH)	20	11	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7
8	7TH THRU 8TH	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9
10	9TH THRU 12TH	3	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
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
21
22		MONTHLY CLASS ADVERAGES																											YTD ADVERAGE		KIDS YTD ADVERAGE
23		OCT			NOV			DEC			JAN			FEB			MAR			APRIL			MAY
24	KIDS	1			0			0			0			0			0			0			0			0			1		7.333333333
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
Lists

Cell Formulas
Range	Formula
C1:AG1	C1	=B1+7
AE24	AE24	=AVERAGEIF(B6:AL10,">0")

 

[PMRetired2012]

As long as the odd numbered rows do not contain any numbers, then that formula should work

+Fluff 1.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
1	MASTER SHEET	13-Oct	20-Oct	27-Oct	03-Nov	10-Nov	17-Nov	24-Nov	01-Dec	08-Dec	15-Dec	22-Dec	29-Dec	05-Jan	12-Jan	19-Jan	26-Jan	02-Feb	09-Feb	16-Feb	23-Feb	02-Mar	09-Mar	16-Mar	23-Mar	30-Mar	06-Apr	13-Apr	20-Apr	27-Apr	04-May	11-May	18-May
2
3
4	NURSERY	1
5
6	KIDS (PREK - 6TH)	20	11	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7
8	7TH THRU 8TH	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
9
10	9TH THRU 12TH	3	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
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
21
22		MONTHLY CLASS ADVERAGES																											YTD ADVERAGE		KIDS YTD ADVERAGE
23		OCT			NOV			DEC			JAN			FEB			MAR			APRIL			MAY
24	KIDS	1			0			0			0			0			0			0			0			0			1		7.333333333
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
Lists

Cell Formulas
Range	Formula
C1:AG1	C1	=B1+7
AE24	AE24	=AVERAGEIF(B6:AL10,">0")

Fluff,
I still wornt work for. I dont want to have 0's in those cells i have mentioned. i want them blank. i guess what im trying for is add all mumbers up in the rows and divide 3 if that is 3 weeks totals there.
i still get that error not #div/0 or not divisable by 0. See if you come up with something that will forsure work.

Thanks

 

[Fluff]

Those 0s are already in the cells as a result of the Sum formulae. You have probably just hidden them & they don't make any difference.

See if you come up with something that will forsure work.

As you can see from the mini sheet in post#15 the formula works.
The only reason you would get a #DIV/0 error is if there are no values greater than 0 in the range.
Try putting this formula in B7

=ABS(B6)=B6

change the B6 to a cell with a number in it, what does the formula return?

 

[PMRetired2012]

Those 0s are already in the cells as a result of the Sum formulae. You have probably just hidden them & they don't make any difference.

As you can see from the mini sheet in post#15 the formula works.
The only reason you would get a #DIV/0 error is if there are no values greater than 0 in the range.
Try putt

ing this formula in B7

=ABS(B6)=B6

change the B6 to a cell with a number in it, what does the formula return?

Those 0s are already in the cells as a result of the Sum formulae. You have probably just hidden them & they don't make any difference.

As you can see from the mini sheet in post#15 the formula works.
The only reason you would get a #DIV/0 error is if there are no values greater than 0 in the range.
Try putting this formula in B7

=ABS(B6)=B6

change the B6 to a cell with a number in it, what does the formula return?

I tried that in B7 and i got ##. YES the 0's are hidden. Is there a way to group ranges and get a average is all im asking? this should be pretty simple unless im missing something here.

 

[Fluff]

You will need to enlarge B7 to see what the formula returned.

Is there a way to group ranges and get a average is all im asking?

Yes it's the formula I showed in post#15 where you can see that it works & returns the correct result.
We need to find out what's up with your data.

 

[PMRetired2012]

You will need to enlarge B7 to see what the formula returned.
Yes it's the formula I showed in post#15 where you can see that it works & returns the correct result.
We need to find out what's up with your data.

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