santoshdream
Apr 5, 2010, 12:12 PM
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
Apr 5, 2010, 02:04 PM

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
Apr 21, 2010, 04:16 AM
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
Apr 21, 2010, 01:44 PM
Thanks for the correction WvR, I did misread the question. Unfortunate use of the disagree button for me, but it was wrong...

WvR
Apr 22, 2010, 03:32 AM