# IF statement formula excluding cells with a 0

1. ## IF statement formula excluding cells with a 0

I have a formula built to provide a weighted average but in some cases there will be zeros in the range that I would like to exclude automatically. The current formula is =((B\$5*C\$15)+(B\$6*C\$16)+(B\$7*C\$17)+(B\$8*C\$18)+(B\$9*C\$19)+(B\$10*C\$20))/(C\$15+C\$16+C\$17+C\$18+C\$19+C\$20)

The numbers in column B refer to the particular percentage (score) and the numbers in Column C refer to the weight given to a particular score.

So, if B5 were to be 0 for some reason, and should not be included in the weighted average, the formula should be =((B\$6*C\$16)+(B\$7*C\$17)+(B\$8*C\$18)+(B\$9*C\$19)+(B\$10*C\$20))/(C\$16+C\$17+C\$18+C\$19+C\$20) and so on if other cells have a 0.

Thanks!

2. ## Re: IF statement formula excluding cells with a 0

=SUMPRODUCT((B\$5:B\$10*C\$15:C\$20))/SUMIF(B\$5:B\$10,"<>0",C\$15:C\$20)

3. ## Re: IF statement formula excluding cells with a 0

Brilliant! Worked perfectly. Thanks a million!

4. ## Re: IF statement formula excluding cells with a 0

There are currently 1 users browsing this thread. (0 members and 1 guests)