IF Formula Help

[Mrs A]

uWhat formula do I use to average columns, only if the values entered are
greater than zero? For instance, column one is 25, column two is 27, and
column three is 0. The answer would be 26. I want the formula to exclude
any entries unless they have a value of at least 1. AND, if there is only
one value; ie column one is 25, but two and three are both 0, then 25 would
be the answer in the final column. Example below:

Subordinate Evaluation Scores
Rating 1 Rating 2 Score
Worker 1 25 27 26 - would be the answer I want
Worker 2 25 0 25 - would be the answer
Worker 3 0 0 0 - would be the answer
Worker 4 0 0
Worker 5 0 0
Worker 6 1 2 1.5 would be the answer
Worker 7 0 0
Worker 8 0 0
Worker 9 0 0
Worker 10 0 0
Worker 11 0 0

Average of All ? - answer would add all of column 3 and average...

Thanks for any help!

 

[PCLIVE]

I'm sure there are other ways, but one way:

=SUM(A2:C2)/(3-COUNTIF(A2:C2,0))

Does that help?
Paul

 

[Don Guillett]

Have you tried average?

=IF(AVERAGE(L2:M2)=0,"",AVERAGE(L2:M2))

 

[muddan madhu]

try this

=IF((A1:B1)=0,LARGE(A1:B1,1),AVERAGE(A1:B1))

array formula , after entering formula Ctrl + Shift + Enter

 

[PCLIVE]

One addition to my formula... because I relized that if they were all zeros,
then you would get an error. Use this modified version.

=IF(SUM(A2:C2)=0,0,SUM(A2:C2)/(3-COUNTIF(A2:C2,0)))

HTH,
Paul

--