AVERAGEIFS TO IGNORE ZEROS

[Craig_Moore]

Hi All,

i am using the formula below to give me the average production tonnes but i need to ignore the zeros is the a way for me to add this in to my current calculation?

=IFERROR(AVERAGEIFS(L3_TONNES,DATE,">="&AG23,DATE,"<="&EOMONTH(AG23,0)),"0")

Thanks

Craig

 

[FlameRetired]

I don't believe AVERAGIFS can do what you require. As the AVERAGE function ignores FALSE you will need to average a nested IF formula. You may also need to array enter this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

=AVERAGE(IF((L3_TONNES<>0)*(DATES>=AG23)*(DATES<=EOMONTH(AG23,0)),L3_TONNES))

I changed your named range DATE to DATES. Excel (365) returns error at my end.

 

[Craig_Moore]

thanks for the reply unfortunately i get an error also, I'm sure I will find some way round it i will just have to play with it for a while

thanks for the response

Craig

 

[Peter_SSs]

Try
=IFERROR(AVERAGEIFS(L3_TONNES,L3_TONNES,"<>0",DATE,">="&AG23,DATE,"<="&EOMONTH(AG23,0)),"0")

Also wondering why you have quote marks around the zero i n the IFERROR value? Don't you want the number zero returned in that case?

unfortunately i get an error

Always a good idea to report exactly what the error is. Could it be a #NAME? error because the suggested formula used the name "DATES" where your name is "DATE"?

 

[Craig_Moore]

Try
=IFERROR(AVERAGEIFS(L3_TONNES,L3_TONNES,"<>0",DATE,">="&AG23,DATE,"<="&EOMONTH(AG23,0)),"0")

Also wondering why you have quote marks around the zero i n the IFERROR value? Don't you want the number zero returned in that case?


Always a good idea to report exactly what the error is. Could it be a #NAME? error because the suggested formula used the name "DATES" where your name is "DATE"?

Hi Peter,

This has helped I'm managing to get the expected results for the dates that have already been inputted
but on the months where i have not data I'm getting a "random" number returned? please see below high lighted in red i would expect this to be 0 as there is no data for those months in the sheet

Also I didn't realise the " " was only for text thanks for the heads up on that

in future i will put on what the error is


thanks for your Reply and help

Craig

MONTHLY TOTAL.xlsx
	AG	AH	AI	AJ
21	AVERAGE MONTHLY DOZ
22		LINE 3	LINE 5	TUBS
23	April	3660	5082	214
24	May	4009	4313	2028
25	June	3580	4791	2010
26	July	3335	4938	2027
27	August	3301	4963	2030
28	September	3301	4963	2030
29	October	3301	4963	2030
30	November	3301	4963	2030
31	December	3301	4963	2030
32	January	3301	4963	2030
33	February	3301	4963	2030
34	March	3301	4963	2030
ALL LINES

Cell Formulas
Range	Formula
AH23:AH34	AH23	=IFERROR(AVERAGEIFS(L3_AMMOUNT,DATE,"<>0"&AG23,DATE,"<="&EOMONTH(AG23,0)),0)
AI23:AI34	AI23	=IFERROR(AVERAGEIFS(L5_AMMOUNT,DATE,"<>0"&AG23,DATE,"<="&EOMONTH(AG23,0)),"0")
AJ23:AJ34	AJ23	=IFERROR(AVERAGEIFS(TUBS_AMMOUNT,DATE,"<>0"&AG3,DATE,"<="&EOMONTH(AG3,0)),"0")

Named Ranges
Name	Refers To	Cells
DATE	='ALL LINES'!$A$3:$A$370	AH23:AJ34
L3_AMMOUNT	='ALL LINES'!$E$3:$E$370	AH23:AH34
L5_AMMOUNT	='ALL LINES'!$J$3:$J$370	AI23:AI34
TUBS_AMMOUNT	='ALL LINES'!$O$3:$O$370	AJ23:AJ34

 

[Peter_SSs]

Those formulas don't look much at all like I suggested.
Your formulas refer to columns that we cannot see so impossible to investigate.