Help needed with a Average formula

Hello all

I have just registered and I’m in need of assistance with a formula if possible, I’m looking for an Average formula which excludes zero’s, blanks cells & doesn’t display #DIV/0!.
Please find table below of what I’m trying to do

Capture.JPG

Mon Tues Wed Thurs Fri Total Average

Paid Meals 235 208 228 671 224

Free Meals 62 54 56 172 57

Supervisors 6 6 6 18 6

Kitchen Staff 0 0 10 10 3

Staff 0 #DIV/0!

Staff Column is stopping me from getting a formula until a enter a figure or 0 and Kitchen staff column is averaging the 3 cells instead of only 1

Average should be 297,

But excel showing average as 290.33

Any help would be much appricated, jpeg attached of table layout

Thanks

Barry

Try AverageIf with an IFERROR to stop errors



=IFERROR(AVERAGEIF(A1:A10,">0"),"")



Welcome to ExcelForum

Thanks Speshul

Seems to have worked perfectly

Many thanks

I edited my response,

=IFERROR(AVERAGEIF(A1:A10,">0"),"")


IFERROR will remove any error messages you get.

try this

=IFERROR(ROUND(AVERAGEIF(B2:G2,">0"),0),0)

	B	C	D	E	F	G	H
1	MON	TUE	WED	THU	FRI	Total	Average
2			235	208	228	671	336
3			62	54	56	172	86
4			6	6	6	18	9
5			0	0	10	10	10