Excel 2003 to 2007 Analysis ToolPack Errors ="#N/A"

[Amit]

Hi,

We have a template sheet we generate in Excel 2003 which uses the Analysis
ToolPack references such as :

=EOMONTH(K42,6)
=XIRR(L42:L47,K42:K47)

Our users have used the templates and edited and saved their copies using
Excel 2003 and we have recently been moving to 2007 and have found that when
they open their versions in 2007 we will often get the error:

File error: data may have been lost.

I have checked the compatibility checker in 2007 and it does not give us any
warnings. We do have some of our add-ins but these seem to behave. Also we
notice that in the name manager that our references will often get deleted
and removed and replaced with #N/A in some of the users 2003 Excel files.


Has anyone had the same occurrence at all.

Many Thanks,

Amit

 

[amcnelis]

Yes, I'm having the same issue using the function EDATE. Did you find a
solution?

Alison

 

[Mike Middleton]

Your problem may depend on how your template sheet uses the Analysis ToolPak
functions, since in Excel 2007 those functions are fully integrated into the
Excel function library. That is, you can use those worksheet functions
without having the "Analysis ToolPak" box checked in the Add-Ins manager.
For example, see

http://blogs.msdn.com/excel/archive/2005/10/20/483205.aspx

- Mike
www.MikeMiddleton.com

 

[Amit]

Hi,

I originally posted when we didn't really understand the original problem.
We soon realised that some functions are automatically included that weren't
in 2003 within the analysis toolpack.

Our problem was worse then we originally thought we opened a support case
with MS, who said that there was a problem with the hidden name ranges within
our sheets; they converted the sheets to html and then xlsb/m. If we opened
our 2003 sheets more than 2 times in 2007 on the 3rd attempt the sheet would
corrupt. We spent quite a long amount of time trying to find out where the
problem was but in the end the quickest solution was we wrote an application
to convert all sheets to the new xlsb/m formats and then manually fix some of
the XIRR references in our sheets that were broken.

Amit

 

[nguyensea]

Does this mean that I should uncheck the Analysis ToolPak box in order to
make EDATE and XIRR functions work? Thanks.

 

[Amit]

Hi,

Try unticking the box and test it, the XIRR function is now a native
Office 2007 function, there is an MS article about this at
http://support.microsoft.com/kb/912719

Thanks,

Amit

 

[Amit]

It might be worth testing at the XIRR and EDATA are native functions in Excel
2007. The following MS article has some more information on this:

http://support.microsoft.com/kb/912719


Thanks,

Amit

 

[amcnelis]

Thank you for response. I tried deselecting tookpak, but same result. Seems
to consistently happen when working with a summary file that is linked to
detailed files. When working with only the detailed files, they are not
'corrupted'.

 

[WinC]

In my spreadsheet, EDATE, NOMINAL AND XIRR got corrupted very so often. I
changed EDATE to =DATE(YEAR(xxx),MONTH(xxx),DAY(xxx)) and NOMINAL to normal
calculation. They work okay, except XIRR that I still have problem with. Any
suggestions? Thanks.