Average across multiple worksheets excluding unfilled cells

[Dbldoc]

I have a workbook consisting of 12 worksheets that represent the months of the year. Each worksheet contains columns representing the days of the week with each day consisting of two adjacent columns (i.e. AB, CD, etc.). The rows to the left of the columns list numerous objects, and each day I manually input data into the two columns that reflect the number of each object sold and the total amount of revenue received from that sale of those objects. At the bottom of each column there is a sum for the total number of objects sold and the total amount of revenue for the day. Not every object is sold each day. I have spent hours trying to devise a formula which will give me the average daily number of sales and the average daily revenue in a running fashion during the month when the formula includes blank cells from future days for which no data currently exists. AVERAGEIF does not work across multiple sheets, and other formulas I have tried including INDEX(FREQUENCY) seem to include the blank cells of future days which filters the average for the month to date data. Any help appreciated, thanks.

 

[Domenic]

I would suggest that you post a small sample of the data, along with the actual expected results.

Also, when posting data, please don't post an image of the data. You can either simply copy and paste the data as text, or better yet use the XL2BB add-in.

Cheers!

 

[Dbldoc]

Thank you. I've uploaded a copy of the worksheet in progress which includes one month, “Jul ‘24”, although eventually will include all 12 months. Sample data added to the “Jul ‘24” sheet. The organization and layout of the worksheet for each month is the same. Data is entered into the “#” column for each day of the week with formulas in the “# RVU’s” column and “Daily Totals” section automatically calculating certain data. Not every row will have data entered each day. The problem I’ve encountered is trying to have running averages in the “Monthly Totals” for the month section (the cells with black fill color and white font in the blue and green sections) that only include the data for days up to and including the current day. The formula in these sections in the “Jul ‘24” sheet averages across all columns including those that have no data yet entered. Eventually I would also like a Yearly Average of certain data as a 13th sheet, averaging the same cell across all 12 (monthly) sheets but encountered the same problem i.e. the averages all months including those with no data yet entered, which skewed the data for previous and current months.

xl2bb.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			OVERNIGHT DATA										MOONLIGHTING
2			Mon 7/1/24		Tue 7/2/24		Wed 7/3/24		Thu 7/4/24
3	RVU	Procedure	#	# RVU's	#	# RVU's	#	# RVU's	#	# RVU's			#	# RVU's	#	# RVU's
4	1.82	CT Abd/ Pelvis w Contrast					4	7.28
5	1.74	CT Abd/Pelvis Oral only	2	3.48
6	2.01	CT Abd/Pelvis w/wo											5	10.05
7	1.74	CT Abd/Pelvis w/o			3	5.22
8	1.00	CT Extremity Lower w
9	1.16	CT Extremity Lower Bilateral w/	2	2.32
10	1.70	CT Head/ Face	2	3.40									5	8.50
11	2.13	CT Head/Orbits
12	1.27	Ct head/brain w/o & w/			3	3.81
13	1.38	CT Neck with	2	2.76
14	1.28	CT Neck w/o					4	5.12
15	1.75	CTA Neck			3	5.25
16	3.50	CTA stroke (H&N)
17	1.78	Mri Brain w	2	3.56
18	2.29	Mri Brain w/ & w/o
19	1.48	Mri Cervical spine wo					4	5.92
20	1.48	Mri Lumbar spine											5	7.40
21	0.59	US Gallbladder RUQ	2	1.18
22	0.99	US OB >=14 wk			3	2.97
23	0.99	US Pelvis Pregnancy 1st Tri < 12 wks					4	3.96
24	1.80	US Scrotum	2	3.60
25	0.56	US Thyroid
26	0.45	US Venous Duplex UE - UC
27	0.16	Xrays (total)	2	0.32	3	0.48	4	0.64					5	0.8
28		IR											3	7.00
29
30	DAILY TOTALS		#	# RVU's	#	# RVU's	#	# RVU's	#	# RVU's			#	# RVU's	#	# RVU's
31		CT Body	4	5.80	3	5.22	4	7.28				CT Body	5	10.05
32		CT Neuro	4	6.16	6	9.06	4	5.12				CT Neuro	5	8.50
33		Total CT	8	11.96	9	14.28	8	12.40				Total CT	10	18.55
34		Total MRI	2	3.56			4	5.92				Total MRI	5	7.40
35		Total US	4	4.78	3	2.97	4	3.96				Total US
36		Total XR	2	0.32	3	0.48	4	0.64				Total XR	5	0.80
37		Total IR										Total IR	3	7.00
38		Σ	16	20.62	15	17.73	20	22.92				Σ	23	33.75
39		Average RVU/Body CT	1.45		1.74		1.82					Average RVU/Body CT	2.01
40		Average RVU/Neuro CT	1.54		1.51		1.28					Average RVU/Neuro CT	1.70
41		Average RVU/All CT	1.50		1.59		1.55					Average RVU/All CT	1.86
42		Average RVU/Mri	1.78				1.48					Average RVU/Mri	1.48
43		Average RVU/US	1.20		0.99		0.99					Average RVU/US
44		% Body CT of total CT	50%	48%	33%	37%	50%	59%				% Body CT of total CT	50%	54%
45		% Body CT of total studies	25%	28%	20%	81%	20%	54%				% Body CT of total studies	22%	30%
46		% Neuro CT of total CT	50%	52%	67%	63%	50%	41%				% Neuro CT of total CT	50%	46%
47		% Neuro CT of total studies	25%	30%	40%	51%	20%	22%				% Neuro CT of total studies	22%	25%
48		% US	25%	23%	20%	17%	20%	17%				% US
49		% Xray	13%	2%	20%	3%	20%	3%				% Xray	22%	2%
50		% IR										% IR	13%	21%
51
52
53	MONTHLY TOTALS (NIGHTS)	Nights	#	# RVU's							MONTHLY TOTALS (MOONLIGHTING)	Moonlight	#	# RVU's
54		CT Body	11	18.30								CT Body	5	10.05
55		CT Neuro	14	20.34								CT Neuro	5	8.50
56		Total CT	25	38.64								Total CT	10	18.55
57		Total MRI	6	9.48								Total MRI	5	7.40
58		Total US	11	11.71								Total US
59		Total XR	9	1.44								Total XR	5	0.80
60		Total IR										Total IR	3	7.00
61		Σ	51	61.27								Σ	23	33.75
62		Average RVU/study										Average RVU/study
63		Body CT	1.66									Body CT	2.01
64		Neuro CT	0.69									Neuro CT	1.70
65		All CT	1.55									All CT	1.86
66		Mri	1.58									Mri	1.48
67		US	1.06									US
68		Xray	0.16									Xray	0.16
69		IR										IR	2.33
70		% of Total CT	#	# RVU's								% of Total CT	#	# RVU's
71		Body CT	44%	47%								Body CT	50%	54%
72		Neuro CT	56%	53%								Neuro CT	50%	46%
73		% of All Studies	#	# RVU's								% of All Studies	#	# RVU's
74		Body CT	22%	30%								Body CT	22%	30%
75		Neuro CT	27%	33%								Neuro CT	22%	25%
76		All CT	49%	63%								All CT	43%	55%
77		% MRI	12%	15%								% MRI	22%	22%
78		% US	22%	19%								% US
79		% Xray	18%	2%								% Xray	22%	2%
80		% IR										% IR	13%	21%
81		Averages per Night Shift Entire Month	#	# RVU's								Averages per ML Shift Entire Month	#	# RVU's
82		CT Body	2.75	4.58								CT Body
83		CT Neuro	4	5.09								CT Neuro
84		Total CT	6	9.66								Total CT
85		Total MRI	2	2.37								Total MRI
86		Total US	3	2.93								Total US
87		Total XR	2	0.36								Total XR
Jul '24

Cell Formulas
Range	Formula
B11	B11	=0.85+1.28
E4:E27,Q4:Q27,O4:O27,K4:K27,I4:I27,G4:G27	E4	=D4*$B4
D31,F31,H31,J31,N31:Q31	D31	=SumByColor(D4:D16,$A$35)
E31,G31,I31,K31	E31	=SumByColor(E4:E16,$A$44)
D32,F32,H32,J32,N32:Q32	D32	=SumByColor(D4:D16,$A$36)
E32,G32,I32,K32	E32	=SumByColor(E4:E16,$A$45)
D33:K33,N56:O56,D56:E56,N33:Q33	D33	=SUM(D31:D32)
D34,F34,H34,J34,N34,P34	D34	=SumByColor(D4:D20,$A$37)
E34,G34,I34,K34,O34,Q34	E34	=SumByColor(E4:E20,$A$46)
D35,F35,H35,J35,N35:Q35	D35	=SumByColor(D21:D26,$A$38)
E35,G35,I35,K35	E35	=SumByColor(E21:E26,$A$47)
D36,F36,H36,J36,N36,P36	D36	=SumByColor(D27,$A$39)
E36,G36,I36,K36,O36,Q36	E36	=SumByColor(E27,$A$48)
N37:Q37	N37	=N28
N38:Q38,N61:O61,D38:K38	N38	=SUM(N33:N37)
N39:N43,P39:P43,D39:D43,F39:F43,H39:H43,J39:J43	N39	=IF(O31,O31/N31,"")
N44:Q44,N49:Q49,D44:K44,D49:K49	N44	=IF(N31,N31/N33,"")
N45:P45,D45:F45,H45,J45	N45	=IF(N31,N31/N38,"")
Q45,G45,I45,K45	Q45	=IF(Q31,Q33/Q38,"")
N46:Q46,D46:K46	N46	=IF(N32,N32/N33,"")
N47:Q47,D47:K47	N47	=IF(N32,N32/N38,"")
N48:Q48,D48:K48	N48	=IF(N35,N35/N38,"")
N50:Q50,D50:K50	N50	=IF(N37,N37/N38,"")
D54:D55	D54	=SumByColor(D31:K31,A35)
E54:E55	E54	=SumByColor(D31:K31,A44)
D57:D59	D57	=SumByColor(D34:K34,A37)
E57:E59	E57	=SumByColor(E34:K34,A46)
N54:N55	N54	=SumByColor(N31:Q31,A35)
O54:O55	O54	=SumByColor(N31:Q31,A44)
N57:N58	N57	=SumByColor(N34:Q34,A37)
O57:O60	O57	=SumByColor(N34:Q34,A46)
N59:N60	N59	=SumByColor(N27:Q27,A39)
D61:E61	D61	=SUM(D56:D59)
D63,D65:D67	D63	=IF(E54,E54/D54,"")
D64	D64	=IF(D55,D55/E55,"")
D68,N63:N69	D68	=IF(D59,E59/D59,"")
D71:E71,N71:O71	D71	=IF(D54,D54/D56,"")
D72:E72,N72:O72	D72	=IF(D55,D55/D56,"")
D74:E74,N74:O74	D74	=IF(D54,D54/D61,"")
D75:E75,N75:O75	D75	=IF(D55,D55/D61,"")
D76:E76,N76:O76	D76	=IF(D56,D56/D61,"")
D77:E77,N77:O77	D77	=IF(D57,D57/D61,"")
D78:E78,N78:O78	D78	=IF(D58,D58/D61,"")
D79:E79,N79:O79	D79	=IF(D59,D59/D61,"")
N80:O80	N80	=IF(N60,N60/N61,"")
D82:D83	D82	=SumByColor(D31:K31,A35)/CountColour(D31:K31,A35)
E82:E83	E82	=SumByColor(D31:K31,A44)/CountColour(D31:K31,A44)
D84	D84	=SumByColor(D33:K33,A41)/CountColour(D33:K33,A41)
E84	E84	=SumByColor(D33:K33,A50)/CountColour(D33:K33,A50)
D85:D87	D85	=SumByColor(D34:K34,A37)/CountColour(D34:K34,A37)
E85:E87	E85	=SumByColor(D34:K34,A46)/CountColour(D34:K34,A46)

 

[Sergius]

Hello!
Using the AVERAGEIF function should help.

 

[Domenic]

I'm not sure I quite understand. If you simply want to average a single cell across multiple worksheets, try...

=AVERAGE('Jul ''24:Jun ''25'!B2)

Note that the formula will ignore blank cells. However, if your other sheets don't already exist, you'll get an error. In this case, I would suggest that you create two new worksheets, and name them First and Last. Then place any and all of the relevant sheets between those two new worksheets. Now those sheets, and any other ones that you place there in the future, will be taken into consideration by the following formula...

=AVERAGE(First:Last!B2)

Does this help? If not, can you please elaborate?