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


******** ******************** ************************************************************************>

Microsoft Excel - Book4 ___Running: xl2002 XP : OS = Windows Windows 2000

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

L16Q16 =

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 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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