XIRR and EDATE functions return as #NA in 2007 in compatability mo

[Wibbs]

XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in
compatability mode. This does not happen everytime, but has happened to a
number of spreadsheets. These provide crucial information to the company and
would like to know how to fix this.

 

[ryguy7272]

Is the Analysis ToolPak add-in installed in 2007? 2003?
http://office.microsoft.com/en-us/excel/HP052090731033.aspx

If it is not installed, install it and retry.

HTH,
Ryan---

 

[Wibbs]

Analysis ToolPak add-in is installed.

Is the Analysis ToolPak add-in installed in 2007? 2003?
http://office.microsoft.com/en-us/excel/HP052090731033.aspx

If it is not installed, install it and retry.

HTH,
Ryan---

 

[JoeU2004]

XIRR and EDATE functions are sometimes changed to #NA in 2007
when saved in compatability mode.

Please clarify the problem statement.

First, do you mean that the problem is seen when the workbook is opened in
Excel 2007? Or do you mean when it is opened in Excel 2003?

Also, is it opened on the same computer, or on a different computer? Is the
file on a shared disk and opened over the network?

Second, do you mean the #N/A error or the #NAME? error?

The #NAME? error suggests that the ATP has not been enabled ("loaded").
Note that the ATP can be installed, but not enabled. See Tools > Add-Ins in
Excel 2003.

The #N/A error indicates that a formula refers to a cell that uses the NA()
function or to a UDF that returns the xlErrNA value. Since these are
programmable errors, you need to look at the worksheet and macro designs to
understand the conditions when these errors are returned. The Evaluate
Formula feature might help. See Tools > Formula Auditing in Excel 2003.


----- original message -----

 

[Wibbs]

Workbooks were originally written in 2003 and are on a network - users work
via TS. Currently in middle of migration, so some users are opening in 2007
as a 2003 compatible file. Seems to be an intermittent problem, sometimes
the XIRR and EDATE functions seem to be OK, othertimes they are replaced by
#N/A.

It is not the case that formula refers to a cell that uses the NA()
function or to a UDF that returns the xlErrNA value.

Currently using Service Pack 1 and operating via Terminal Services.

Thanks

 

[JoeU2004]

Workbooks were originally written in 2003 and are on a network - users
work
via TS. Currently in middle of migration, so some users are opening in
2007
as a 2003 compatible file. Seems to be an intermittent problem, sometimes
the XIRR and EDATE functions seem to be OK, othertimes they are replaced
by #N/A.

That is much clearer. One detail remains unclear to me: who is seeing the
intermittent #N/A error: the Excel 2007 user or the Excel 2003 user?

Have you observed this "intermittent" behavior yourself, or are people
simply reporting it to you?

Meaning no disrespect, but in my experience, most of the time that customer
report "intermittent" behavior, it is really because circumstances changed;
for example, they changed the values of some parameters.

Since the users are using Terminal Services to access the file and/or Excel,
we can rule out the network as a source of intermittent behavior. All the
processing is taking place on the central computer.

I am not aware of any circumstances where XIRR and EDATE might return the
#N/A error, at least not according to Excel 2003 documentation. Does the
Excel 2007 Help pages indicate any circumstances when that might happen?

I see that this problem has been reported and discussed several times since
2007. I have not found any resolution of the problem.

However, in one case, forcing recalculation remedied the error
(ctrl-alt-F9).


----- original message -----

 

[JoeU2004]

I had a theory. It did not pan out in my experiments. But my situation is
probably very different from yours, so it might be worth pursuing, if you
think it might be applicable.

Do the workbooks that exhibit the problem have links to other workbooks?

If they do, I wonder how they behave under each of the following conditions:
(a) the link target has a new file name (i.e. the link no longer points to
an Excel workbook); (b) the link target is currently open writable (i.e. not
read-only) by another user; and (c) the link target cells no longer have
valid values for XIRR or EDATE.

None of these presents a challenge in my environment; Excel is well-behaved.
But I have WinXP and Excel 2003; you have Excel 2007 and perhaps Vista.
Also, you might be using a different type of file system.

If that does not pan out, look for factors that might be common among the
workbooks that exhibit problems. Often, the perception of "intermittent"
behavior boils down to a specific set of conditions that cause a very
reproducible behavior, once it is understood.

When you finally isolate the problem, I hope you will post an update to this
thread.


---- original message -----