My XNPV formulas are being replace with =#n/a. Why

[DKHUtah]

I recently started running Excel 2007. I have a file that uses XNPV formulas
and YEARFRAC formulas. When I opened it, I had an Error: data lost message
and all my XNPV and YEARFRAC formulas were replaced with =#N/A, but the
previous value remained displayed. Anyone know what is happening?

 

[Bill Manville]

The effect is presumably caused by the migration of the Analysis
toolpak into Excel 2007 - but I don't have a solution for you.

There have been suggestions that having the Analysis Toolpak - VBA
add-in installed can cause problems. Does this apply to you?

Bill Manville
MVP - Microsoft Excel, Oxford, England

 

[amcnelis]

I don't see this thread continuing after 8/24, but I too am having a problem
with formulas (EDATE) that used the Analysis Toolpak in 2003 becoming #n/a in
2007 after accessing the file 2 or 3 times. I have tried deselecting the
Analysis Toolpak and have gone back to the install and made it unavailable
but I continue to experience the issue. I have not found this issue being
addressed in any other area. Do you have any additional information at this
point?

 

[mikawendy]

Sorry to say I don't have any solutions, but my office is having the same
problems with the WORKDAY formula. We have multiple Excel docs that link to a
summary spreadsheet with totals, schedules, etc. We use Analysis Tookpak as
well.

Most of our spreadsheets are created on a machine running Excel 2007 (PC);
others are Mac and run Excel 2004. (The spreadsheets are saved down to .xls
format instead of .xslx, and do not use any features of Excel 2007 that
aren't available in earlier versions of Excel.)

I'll repost here if I find more info. (The formula replacement with =#N/A is
driving us nuts because there is no pattern to when it will happen; some docs
are always fine and others we wind up having to rebuild a number of times.)

 

[DAVEY]

I too am having issues with my workday formulas. I keep my holidays from the
previous, current, and next years on a separate file. My formulas link to
the cells containing the values for the holidays for all 3 years. This
eliminates the need to update all of the cells in all of my spreadsheets
containing workday formulas.
After upgrading to Excel 2007, I have been having problems. Thinking is was
my lookup formulas, I started copying the various source spreadsheets to the
main spreadsheets as subsequent tabs. This has seemed to work on most of my
files, but it's caused problems of a different sort: how to replace the
values of some frequency w/out losing the reference to my links.
Unfortunately, my workday formulas are still causing problems. The problem
happens randomly w/out any rhyme or reason.
I cannot find a special add-in that would fix this, so I'm wondering if it's
a conversion issue from 97-2003 files to 2007.
I need answers to this problem SOON! Re-work is taking up too much of my
time.
HINT: One thing I did start doing is finding a cell that is outside of my
spreadsheet and pasting the workday formula I need into it, but putting an
apostrophe in front of the formula. This now looks like text, so Excel won't
replace with "=#N/A" or "=N/A". Anytime I need to fix my spreadsheet again,
I simply copy the formula (minus the apostrophe) into the appropriate cells.
This is far from a perfect solution, but it saves me a lot of time.