Ignoring Blank cells while calculating average.

[Nikita005]

Hi,

I am trying to get average using following :

=COUNTIF(D3:D60,"Pass")-COUNTBLANK(D3:D60)/ROWS(D3:D60)

But I am getting 3989.66%.
Can you please help me correct the formula?

Thanks
Nikita

 

[Joe4]

Welcome to the Board!

You are missing parentheses, so it divides the 2nd term by the 3rd term first, and then subtracts it from the first.
I think this is what you want:

[COLOR=#333333]=(COUNTIF(D3:D60,"Pass")-COUNTBLANK(D3:D60))/ROWS(D3:D60)[/COLOR]

 

[MarcelBeug]

Another suggestion:

=COUNTIF(D3:D60,"Pass")/(ROWS(D3:D60)-COUNTBLANK(D3:D60))

 

[Nikita005]

Thanks alot

 

[FDibbins]

If the cells are really blank/empty, AVERAGE() should ignore them anyway?

 

[MarcelBeug]

You're welcome.

By the way: whom are you refering to?

 

[Joe4]

If the cells are really blank/empty, AVERAGE() should ignore them anyway?

My initial thought, but upon further inspection of their formula, it looks like the range has text, and they are trying to count/average the number of cells containing certain text (i.e. "PASS").