need calculation that considers a blank cell to be '100'

[greyscale]

Hi,

I have a spreadsheet that counts the SVL achieved in intervals throughout the day. The data from rows 4-72 are pasted in from another system. My calculation in J3 ignores cells that are blank, however, I would prefer to consider a blank cell to be '100' as intervals with no data are bringing down our results. Does anyone know how I can do that within cell J3 without the need to manipulate the data in the rows below?

The document is 2003 compatible but I can make it a 2010 doc if that gives more flexibility.

Thanks in advance

[vandan_tanna]

there is nothing in cell J3!

[greyscale]

Argh! sorry! I meant J2. I need the calculation to return 58.82% instead of 56.92%

[vlady]

=(countif(j5:j72,">="&90)+countif(j5:j72,"="&""))/(countif(j5:j72,">="&0)+countif(j5:j72,"="&""))

[greyscale]

Beautiful! thank you vlady. You've come to my rescue once again ;-)

[vlady]

welcome.

[MarvinP]

How about using the CountBlank() for an easier formula?

=(COUNTIF(J5:J72,">="&90)+COUNTBLANK(J5:J72))/(COUNTIF(J5:J72,">="&0)+COUNTBLANK(J5:J72))

[vlady]

arrg, forgot that one. thanks marvin.