Average a row of data without zeros

[benkong2]

My sheet has 4 rows of data and a total row that is supposed to average the results over a week. example:
week1 100
week2 125
week3 140
week4 98
Total =Average(A1:a4)
Problem is if week 2,3 and 4 are empty or my user unknowingly enters a zero the average is incorrect. What I actually want to do is say if week1 is not empty or greater than 0 the average = week1. If week1 and week2 are not empty or greater than 0 then average week1 and week2. Is this possible?

 

[anno]

http://www.mrexcel.com/archive2/messages/4820.html

 

[plettieri]

Try this

Book4
	E	F	G	H	I	J	K	L	M	N	O	P	Q
15
16	100	100	100	100	100	100	100	0	100	100	100	100	100
17
18
Sheet1

You need to entry this formular using Control/Shift/Enter keys

pll
This message was edited by plettieri on 2002-10-18 13:59

 

[IML]

Another formula option would be
=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

which should work about the same as the array
=AVERAGE(IF(A1:A4,A1:A4))
with your data, except no error message if no entries are made in A1:a4