IF Formula Help
 

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!

IF Formula Help
 

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

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

Does that help?
Paul

--

"Mrs A" <Mrs wrote in message
...
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!

IF Formula Help
 

Have you tried average?

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mrs A" <Mrs wrote in message
...
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!

IF Formula Help
 

try this

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

array formula , after entering formula Ctrl + Shift + Enter



On Jun 12, 10:13*pm, Mrs A <Mrs wrote:
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! * *

IF Formula Help
 

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

--

"PCLIVE" wrote in message
...
I'm sure there are other ways, but one way:

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

Does that help?
Paul

--

"Mrs A" <Mrs wrote in message
...
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!