Average Function

[vmjan02]

I have this table and not sure as why the average is taking the 0 value cell as it should not take any cell having 0 value for average calcu, any idea.

NAF_Automation_v1.xlsm
	A	B	C	D	E	F
3	1	246	31069	972	4118	85.3%
4	2	36	4209	49	912	80.8%
5	3	1157	128273	853	19390	85.7%
6	4	105	12133	88	1632	86.9%
7	5	30	5053	93	673	86.4%
8	6	115	12114	378	2263	81.5%
9	7	186	44031	1240	5346	86.7%
10	8	827	172193	4918	22255	86.0%
11	9	88	7897	103	1172	85.3%
12	10	30	6419	50	848	87.4%
13	11	164	22016	160	3074	86.6%
14	12	2830	214863	2242	39941	82.7%
15	13	152	26048	739	2768	87.7%
WeeklyCalcu

Cell Formulas
Range	Formula
B3:E15	B3	=AVERAGE(IF(J3 > 0, J3), IF(O3 > 0, O3), IF(T3 > 0, T3), IF(Y3 > 0, Y3), IF(AD3 > 0, AD3), IF(AI3 > 0, AI3), IF(AN3 > 0, AN3))
F3:F15	F3	=C3/SUM(B3:E3)

 

[bobsan42]

the Average function does not take into account empty cell values, but if you feed it with 0s it will calculate them too.
However, in the data you presented i cannot see the actual problem.

 

[vmjan02]

the Average function does not take into account empty cell values, but if you feed it with 0s it will calculate them too.
However, in the data you presented i cannot see the actual problem.

I am using this formula and it is working perfect.
Thank you for your advice, it help me a lot.

=SUM((J3>0)*J3, (O3>0)*O3, (T3>0)*T3, (Y3>0)*Y3, (AD3>0)*AD3, (AI3>0)*AI3, (AN3>0)*AN3) / SUM((J3>0), (O3>0), (T3>0), (Y3>0), (AD3>0), (AI3>0), (AN3>0))

 

[bobsan42]

IF(J3 > 0, J3)

if these are values in cells rather than parts of a tedious formula you will get the expected average. And your formulas will be easier to construct and read

 

[Peter_SSs]

Are there headings in columns J:AR? If so, can you post J1:AR15 with XL2BB? There may be a simpler formula.

 

[vmjan02]

Are there headings in columns J:AR? If so, can you post J1:AR15 with XL2BB? There may be a simpler formula.

here you go

NAF Week - 32 - 2024.xlsx
	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	AM	AN	AO	AP	AQ
1										11-Aug-24					12-Aug-24					13-Aug-24					14-Aug-24					15-Aug-24					16-Aug-24					17-Aug-24
2	Data	DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS	Percentage				DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS		DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS		DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS		DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS		DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS		DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS		DEGRADED	GOOD	INTERRUPTED	REDUCED_ROBUSTNESS
3	1	288	36247	1134	4805	85.3%				199	36212	1148	4768		554	36228	872	4773		163	36747	808	4776		275	36250	1128	4807		342	35711	1458	4909		0	0	0	0		192	36333	1390	4794
4	2	42	4910	58	1065	80.8%				47	4917	43	1070		36	4960	31	1068		39	4927	31	1089		47	4898	62	1070		43	4911	37	1063		0	0	0	0		42	4847	141	1027
5	3	1350	149652	996	22621	85.7%				1391	147918	1064	23140		1319	149241	910	22790		1354	149660	964	22561		1319	149909	964	22267		1302	150453	1007	22313		0	0	0	0		1415	150728	1064	22657
6	4	123	14155	103	1904	86.9%				119	14119	115	1910		119	14156	95	1894		126	14158	94	1892		127	14185	91	1895		125	14137	104	1904		0	0	0	0		122	14176	119	1928
7	5	35	5895	109	785	86.4%				37	5884	84	793		28	5889	123	772		36	5874	123	789		35	5911	98	789		43	5909	95	776		0	0	0	0		28	5903	128	793
8	6	134	14134	442	2641	81.5%				114	14162	389	2626		103	14229	318	2665		113	14271	348	2642		153	13964	585	2619		166	13925	575	2676		0	0	0	0		157	14250	434	2615
9	7	217	51370	1447	6237	86.7%				154	51061	1588	6301		232	51311	1353	6247		179	51466	1335	6262		312	51289	1565	6139		266	51374	1392	6204		0	0	0	0		156	51716	1448	6269
10	8	965	200892	5737	25964	86.0%				911	200099	5776	26026		1121	200167	5640	25825		1006	200627	5933	26033		1011	201081	5795	25958		871	200625	5765	26017		0	0	0	0		869	202751	5514	25923
11	9	102	9213	120	1368	85.3%				108	8863	290	1423		102	9225	85	1357		101	9268	85	1364		93	9298	80	1351		108	9265	99	1346		0	0	0	0		101	9359	82	1365
12	10	35	7489	58	989	87.4%				34	7563	43	1012		32	7162	56	962		22	5623	68	844		38	8200	52	1035		42	8186	62	1016		0	0	0	0		39	8197	66	1064
13	11	191	25686	187	3587	86.6%				182	25661	183	3571		178	25699	173	3577		187	25676	191	3582		187	25663	196	3607		201	25652	196	3597		0	0	0	0		211	25763	182	3586
14	12	3302	250673	2616	46598	82.7%				3170	253389	2366	48054		3250	255529	2963	49011		3395	240949	2746	44529		3190	249060	2313	45240		3094	242822	2247	45036		0	0	0	0		3713	262289	3059	47717
15	13	178	30389	862	3229	87.7%				76	30301	778	3273		96	30398	796	3281		96	30314	1050	3154		249	30379	880	3213		238	30441	766	3239		0	0	0	0		312	30500	901	3215
NAF Week - 32 - 2024

Cell Formulas
Range	Formula
A5:A15	A5	=A4+1

 

[Peter_SSs]

here you go

Thanks. For the averages, try this in B3 copied across and down

=AVERAGEIFS($J3:$AQ3,$J$2:$AQ$2,B$2,$J3:$AQ3,"<>0")

 

[vmjan02]

@Peter_SSs
thanks man, i missed this completely.

 

[Peter_SSs]

Glad to help.