How do I add an exclude counting zero to an existing formula?

[JimmyW74]

Firstly, I must say I am a real novice at all of this!

I have the following formula: = COUNTIF( G5:G29, ">79")/COUNTA( G5:G29 )*100

It counts how many results from students are 79 and below and then gives me an overall group percentage. Occaisionally we have absentees and so a 0 is recorded- I don't want the zero to be counted but can't figure out how to put this into the formula. Everything I try doesn't work.

Can anyone help me?

 

[snjpverma]

=COUNTIF(G5:G29,">79")/(COUNTA(G5:G29)-COUNTIF(G5:G29,0))*100

 

[JimmyW74]

=COUNTIF(G5:G29,">79")/(COUNTA(G5:G29)-COUNTIF(G5:G29,0))*100

Thankyou so much!

 

[Peter_SSs]

Welcome to the MrExcel board!

What about just ..

=COUNTIF(G5:G29,">79")/COUNTIF(G5:G29,">0")*100

 

[JimmyW74]

Welcome to the MrExcel board!

What about just ..

=COUNTIF(G5:G29,">79")/COUNTIF(G5:G29,">0")*100

Thanks...a further question. That formula works fine for that column of the sheet. But if I apply it it to another column and change the range to from G5:G29 to K5 to K29, I get this error message: Function DIVIDE parameter 2 cannot be zero.

How can fix this?

 

[Peter_SSs]

I get this error message: Function DIVIDE parameter 2 cannot be zero.

That message does not look familiar to me. Are you using Excel, Google sheets or something else?

 

[snjpverma]

The error might be occurring in Google sheets when the second parameter of the DIVIDE function (the denominator) is zero or blank. This means that either the COUNTIF(G5:G29,“>0”) or the COUNTIF(K5:K29,“>0”) returns zero in your formula. You can check this by entering these functions separately in different cells and see what values they return. The second value (the value after the division symbol “/”) cannot be zero, since dividing by zero is infinite or undefined mathematically.