Average a text file issue Excel 2003

[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

 

[Dave Peterson]

It surprises me that this actually worked at all.

In my simple test, I create a workbook with just one worksheet. I put test date
in G1:G29. I put this formula in another workbook.

=SUM(book1!$G$1:$G$29)

When I closed book1 and saved it as a .dat file, I got this formula.

=SUM('C:\My Documents\excel\[book1.Dat]book1'!$G$1:$G$29)

But excel wouldn't update it unless the workbook was open. I got an error
message that said excel couldn't update one or more links in this workbook when
I just pretended to edit the formula (F2 followed by an edit).

The only way I know how to do this is to open the .dat file, convert to .xls and
save it and have those links point at the .xls file.

I've never used it, but you may want to try using:
Data|query to retrieve values from a text file
(I don't know if this would work, though.)

One more alternative:
I would think that you could write a UDF that parsed your text file into just
the stuff you cared about and then brought the values back (sum/count =
average), but with 1000's, I would think that even that would be a bear to
update.

And you'd have to know how to parse the line, too. (comma/space/tab delimited
or fixed width).

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

 

[Dave Peterson]

Ps. I used xl2002 in my testing.

When I got the message that the link couldn't be updated, the value didn't
change. Any chance that the values never changed when those files were closed
(and the averages would have been in error)???

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

 

[Guest]

Some of these are updating just fine and it is consistant that the links that
do not average and update exceed the range of data in the file. all the other
links update and calculate just fine. What is a UDF? Can a DAT file be
converted easily?

It surprises me that this actually worked at all.

In my simple test, I create a workbook with just one worksheet. I put test date
in G1:G29. I put this formula in another workbook.

=SUM(book1!$G$1:$G$29)

When I closed book1 and saved it as a .dat file, I got this formula.

=SUM('C:\My Documents\excel\[book1.Dat]book1'!$G$1:$G$29)

But excel wouldn't update it unless the workbook was open. I got an error
message that said excel couldn't update one or more links in this workbook when
I just pretended to edit the formula (F2 followed by an edit).

The only way I know how to do this is to open the .dat file, convert to .xls and
save it and have those links point at the .xls file.

I've never used it, but you may want to try using:
Data|query to retrieve values from a text file
(I don't know if this would work, though.)

One more alternative:
I would think that you could write a UDF that parsed your text file into just
the stuff you cared about and then brought the values back (sum/count =
average), but with 1000's, I would think that even that would be a bear to
update.

And you'd have to know how to parse the line, too. (comma/space/tab delimited
or fixed width).

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

 

[Guest]

Another strange occourance, is when I open the link manager and open source
on one of the links that updates, all of the data seems to fall into the
correct fields. When I open the source on the links with the #REF! errors it
seems to have imported all the data for each row into the first cell of the
row. The customer claims all this was working fine before he upgraded to
2003, I have to trust that this is correct.

Ps. I used xl2002 in my testing.

When I got the message that the link couldn't be updated, the value didn't
change. Any chance that the values never changed when those files were closed
(and the averages would have been in error)???

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

 

[Dave Peterson]

A UDF is a user defined function. It's VBA code that can be written to open the
..dat file, read through the records, extract the data and return the value
(average) to the cell with the formula in it.

Can .dat files be converted easily?

Individually, I'd say yeah--since you said you could just open them and they go
to the correct columns.

But if you really have 1000's, I think you'd need a macro to do it--and adjust
the links.

Some of these are updating just fine and it is consistant that the links that
do not average and update exceed the range of data in the file. all the other
links update and calculate just fine. What is a UDF? Can a DAT file be
converted easily?

It surprises me that this actually worked at all.

In my simple test, I create a workbook with just one worksheet. I put test date
in G1:G29. I put this formula in another workbook.

=SUM(book1!$G$1:$G$29)

When I closed book1 and saved it as a .dat file, I got this formula.

=SUM('C:\My Documents\excel\[book1.Dat]book1'!$G$1:$G$29)

But excel wouldn't update it unless the workbook was open. I got an error
message that said excel couldn't update one or more links in this workbook when
I just pretended to edit the formula (F2 followed by an edit).

The only way I know how to do this is to open the .dat file, convert to .xls and
save it and have those links point at the .xls file.

I've never used it, but you may want to try using:
Data|query to retrieve values from a text file
(I don't know if this would work, though.)

One more alternative:
I would think that you could write a UDF that parsed your text file into just
the stuff you cared about and then brought the values back (sum/count =
average), but with 1000's, I would think that even that would be a bear to
update.

And you'd have to know how to parse the line, too. (comma/space/tab delimited
or fixed width).

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

 

[Dave Peterson]

(to me) this sounds like that the links were never updated after they were
created.

xl2002+ likes to recalculate workbooks when you open them if they were created
in
earlier versions of excel.

If you choose not to update the links, then xl2002+ return #ref! errors. In
earlier versions, the value stayed the same--but wasn't updated.

I don't have xl2k available, but I'd bet that although the formulas looked ok,
they were really out of date once the .dat file got changed--if the .dat file
wasn't opened subsequently.

===
When you did the link stuff (edit|links???), did you click the "update values"
button or the "open source" button.

I got errors with the "update values" button. I got updated values if I did
"open source".

(If you can look at the .dat file in a text editor, can you describe what you
see. I'm guessing that it's simply a tab delimited text file (or something like
this).)

And if you want a macro to open each .dat file and resave each as .xls, you'll
have to give a little information.

Are all the .dat files in one folder?
And what is the layout of those .dat files?

Another strange occourance, is when I open the link manager and open source
on one of the links that updates, all of the data seems to fall into the
correct fields. When I open the source on the links with the #REF! errors it
seems to have imported all the data for each row into the first cell of the
row. The customer claims all this was working fine before he upgraded to
2003, I have to trust that this is correct.

Ps. I used xl2002 in my testing.

When I got the message that the link couldn't be updated, the value didn't
change. Any chance that the values never changed when those files were closed
(and the averages would have been in error)???

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