AVERAGEIF function works in 2007 but returns error in Excel 2008

[dr.nixon]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a complicated spreadsheet that depends on averaging values for a scientific study. The values being averaged are the result of other calculations in the spreadsheet. Because some data is missing, these cells return a value of #N/A. I am using the AVERAGEIF function to ignore cells that have no value. The formula that works is:

=AVERAGEIF(cell:range,"<>#N/A")

This formula works perfectly in Excel 2007. No issues. When I opened the .xlsx file in Excel 2008, every single one of the averages using this function return a #DIV/0 error. I can't fix it. This works correctly in the Windows version of Excel and fails miserably on my Mac.

As far as I can see, my only alternatives are (a) abandon Mac Excel and use Windows, or (b) hand-edit formulas in 17,000 cells, because Excel 2008 is buggy. Any suggestions?

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a complicated spreadsheet that depends on averaging values for a
scientific study. The values being averaged are the result of other
calculations in the spreadsheet. Because some data is missing, these cells
return a value of #N/A. I am using the AVERAGEIF function to ignore cells that
have no value. The formula that works is:

=AVERAGEIF(cell:range,"<>#N/A")

This formula works perfectly in Excel 2007. No issues. When I opened the .xlsx
file in Excel 2008, every single one of the averages using this function
return a #DIV/0 error. I can't fix it. This works correctly in the Windows
version of Excel and fails miserably on my Mac.

As far as I can see, my only alternatives are (a) abandon Mac Excel and use
Windows, or (b) hand-edit formulas in 17,000 cells, because Excel 2008 is
buggy. Any suggestions?

In a short test, I find that averageif seems to work fine. I only get a
#DIV/0 error if there are no actual data values in the range. Can you
isolate this to a single formula and range, and see if that works on a
separate worksheet? How large are the ranges?

 

[Pat McMillan]

And if you're able to send a spreadsheet that exhibits this problem to
myself ([email protected]) and Joe LeBlanc ([email protected]), we
would be glad to investigate.

Thanks,

Pat

 

[dr.nixon]

I did a test of the function and it looks like the AVERAGEIF() works as expected unless one of the cells referenced contains another function that returns an N/A value. If a cell with a value of N/A is referenced directly, it is correctly ignored. I have put together a small testcase file and will send it as requested.