[santoshdream]

Hi,

I am working on a spread sheet but unable to come up with the formula... here is what I want from the formula.

Lets assume, in Col A; A to E are the various categories and in Col B, there is a score (0, 3, 6 & 9), In col C I need the average of Col B but if any of the score is "0" then the formula should not do the average but give "0" as a result. Attached file with some more details...
A
B
C
D
E

[JBeaucaire]

Put this in C2:

=IF(COUNTIF($B$2:$B$7, ">0")=0, 0, SUM($B$2:$B$7) / COUNTIF($B$2:$B$7, ">0"))

[WvR]

The formula =IF(COUNTIF($B$2:$B$7, ">0")=0, 0, SUM($B$2:$B$7) / COUNTIF($B$2:$B$7, ">0")) only solves the problem not to include a zero value in the average calculation
The formula =IF(MIN($B$2:$B$7)=0, 0, AVERAGE($B$2:$B$7)) will not calculate an average if any of the values is zero but will rather display "0". This is if all other values are positive

[JBeaucaire]

Thanks for the correction WvR, I did misread the question. Unfortunate use of the disagree button for me, but it was wrong...

[WvR]

Sorry about that, I did go through some of your other answers and I am really impressed with your work.