G
Guest
I am assisting a customer who was recently upgraded from Office 2000 to
Office 2003. The customer claims this formula worked fine in Excel 2000. He
is averaging data files which are text with a *.dat extention. The formulas
look like this: =AVERAGE('C:\Path_to_data_file\[R0131.dat]R0131'!$G$2:$G$120)
When the file is opened he is prompted as he should be to update links. If
the *.dat file has data in the g2:g120 range, the average formula works fine
and updates when the links update. If the *.dat file has any null values
towards the end of the file or if data is in the g2:g110 range a #REF! error
is returned. If the links are opened, and the source opened, then the formula
seems to update properly. There are thousands of these links in a sheet, the
*.dat files are dynamic and it is not practical to open the source on every
link. The *.dat files are constantly updated so it is not practical to
convert them to another format. Is there any way to tell this formula to
ignore null values in this data range? Is there another fix for this issue?
TIA
George
Office 2003. The customer claims this formula worked fine in Excel 2000. He
is averaging data files which are text with a *.dat extention. The formulas
look like this: =AVERAGE('C:\Path_to_data_file\[R0131.dat]R0131'!$G$2:$G$120)
When the file is opened he is prompted as he should be to update links. If
the *.dat file has data in the g2:g120 range, the average formula works fine
and updates when the links update. If the *.dat file has any null values
towards the end of the file or if data is in the g2:g110 range a #REF! error
is returned. If the links are opened, and the source opened, then the formula
seems to update properly. There are thousands of these links in a sheet, the
*.dat files are dynamic and it is not practical to open the source on every
link. The *.dat files are constantly updated so it is not practical to
convert them to another format. Is there any way to tell this formula to
ignore null values in this data range? Is there another fix for this issue?
TIA
George