Formula needed to get average of values

Hi All,

I have an excel sheet with values as below

ColA	ColB	ColC	ColD	ColE
15	            20		25
0		0		0

I need to get the average values of each row in another sheet. As shown above, the values will be there in alternate cells like ColA,ColC etc

Requirement is:

1. If any of the alternate cells has value then need to take the average
2. If alll alternate cells are blank then the resultant cell should also be blank.
3. If all the alternate cell values are zero then resultant cell should be blank.

Currently I am using the below formula which is satisfying first two of my requirement but not 3rd.

The formula is

=IF(COUNT(Shee1!D8|Sheet1!F8|Sheet1!H8|Sheet1!J8|Sheet1!L8|Sheet1!N8|Sheet1!P8|Sheet1!R8|Sheet1!T8|Sheet1!V8)>0|AVERAGE(Sheet1!D8|Sheet1!F8|Sheet1!H8|Sheet1!J8|Sheet1!L8|Sheet1!N8|Sheet1!P8|Sheet1!R8|Sheet1!T8|Sheet1!V8)|"")

If the values in all the aternate cells are zero, then as per the formula above it will just take the average which is zero and place it in the resultant cell. But per my requirement the resultant cell should be blank in this case. Please help me how to do this.

Originally Posted by FDibbins

Based on your sample, try this, copied down...

=IF(COUNT(A2:E2)=0,"",AVERAGE(A2:E2))

Hi,

Thanks for your reply but the problem with your formula is it will try to average all the values from A2 to E2 right? but I want to get average values from alternate cells..

like, average of values from A2,C2,E2,F2 etc..

And if there is no value in those cells then resultant cell should be blank.
If all the values is zero in the cell then the resultant cell should be blank.
It should place the average of values only if the values present in those cells are greater than zero..