#NAME? error because Excel 2007 function not recognized in Excel 2003

Question

I have the following formula in Excel 2007 and it works fine:

=IFERROR((COUNTIF($F$9:$F$3774,4))/(COUNT($F$9:$F$3774)),"")

I used IFERROR because until data is entered in the spreadsheet column F is blank, which caused the original formula ((COUNTIF($F$9:$F$3774,4))/(COUNT($F$9:$F$3774))) to display a #DIV/O error.  Any of the cells in column F could potentially contain a zero as part of data entry.

However, I need to downsave the file so colleagues that still have Excel 2003 can use the spreadsheet.  The compatibility checker indicates that "when recalculated in earlier versions the function will return a #NAME? error instead of their current results."

Any suggestions on how I can change the formula so it will work in both 2007 and 2003?

Answer 1

I have the following formula in Excel 2007 and it works fine:

=IFERROR((COUNTIF($F$9:$F$3774,4))/(COUNT($F$9:$F$3774)),"")

I used IFERROR because until data is entered in the spreadsheet column F is blank, which caused the original formula ((COUNTIF($F$9:$F$3774,4))/(COUNT($F$9:$F$3774))) to display a #DIV/O error.

However, I need to downsave the file so colleagues that still have Excel 2003 can use the spreadsheet.  The compatibility checker indicates that "when recalculated in earlier versions the function will return a #NAME? error instead of their current results."

Any suggestions on how I can change the formula so it will work in both 2007 and 2003?

Here's one way...

=IF(COUNT($F$9:$F$3774),COUNTIF($F$9:$F$3774,4)/COUNT($F$9:$F$3774),"")

--

Biff

Microsoft Excel MVP

Answer 2

Perfect!  Thank you!

You're welcome. Thanks for the feedback!

--

Biff

Microsoft Excel MVP

Answer 3

Perfect!  Thank you!