Formula replaced with =#N/A in 2007 - why?

[Alan Smith]

Hi All,

I have recently moved from Excel 2003 to 2007, and noticed a problem with
one of my files. The formula is used to calculcate revenues on projects based
on the number of work days applicable in the month, and is as follows:

=IF($U6>BD$2,0,IF($V6<BD$1,0,IF(AND($U6>=BD$1,$V6>BD$2),SUM($AO6*(NETWORKDAYS($U6,BD$2)/NETWORKDAYS($U6,$V6))),IF(AND($U6<BD$1,$V6<=BD$2),SUM($AO6*(NETWORKDAYS(BD$1,$V6)/NETWORKDAYS($U6,$V6))),IF(AND($U6<BD$1,$V6>BD$2),SUM($AO6*(NETWORKDAYS(BD$1,BD$2)/NETWORKDAYS($U6,$V6))),$AO6)))))

Where:
U6 = project start date;
V6 = project end date;
BD1 = month start date, and
BD2 = month end date.

The formula worked fine in 2003, and works in 2007 now that I have redone
it. The problem was that, when I opened the sheet this morning (in
compatability mode initially), all the formulas had been replaced with =#N/A.

Does anybody know why that happened, and how I can avoid it, or correct it
in future?

Thanks in advance,

Alan

 

[Stan Brown]

Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith

when I opened the sheet this morning (in
compatability mode initially), all the formulas had been replaced with =#N/A.

I suspect you have a #NA somewhere in a precedent cell. Either trace
the logic back through the cells and arrays that are used in your
formulas, or use the Formula Auditing feature.

 

[Sean]

I have also just migrated to Excel 2007 and am experiencing the same
problem. Any formula using Networkdays() is replaced with =#N/A. These
formulae always worked in 2003 but now result in a "File Error: Data may have
been lost" message when I open the file in 2007, along with all the
Networkdays() formulae being replaced with =#N/A. There are several threads
on this topic, but so far no answers.

Sean

 

[Roger Govier]

Hi

Press the round office button>Excel Options>Addins>Go>check Analysis
Toolpak>OK

 

[iliace]

This will happen with formulas using Analysis ToolPak functions,
because those are now built-in Excel functions without an add-in. I
haven't found a workaround, but then again, I hardly used ATP
worksheet functions prior to 2007.

 

[Sean]

Roger;
I'm not sure what you are suggesting. Excel 2007 has these functions
built-in, so the add-in shouldn't be necessary. This does not happen on all
files and it does not happen every time on the troublesome ones. So the
Networkdays() function is working most of the time, but now and again just
disappears!
Sean

 

[Sean]

Iliace;

I have been wondering if that might have something to do with it. When I
first installed XL2007, I added in the Analysis ToolPack, assuming that it
would be necessary (there was no warning that I should not). When I started
getting the problems, I checked the web and discovered that Networkdays() was
now a native function. I removed the TPA but the problems persist. I'm
thinking now that I should uninstall and reinstall Office 2007.

You say you haven't found a workaround - does that mean that you have also
experienced the problem?

Sean

 

[Niek Otten]

I assume that rebuilding the formula chain will solve the problem; CTRL+ALT+SHIFT+F9

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Iliace;
|
| I have been wondering if that might have something to do with it. When I
| first installed XL2007, I added in the Analysis ToolPack, assuming that it
| would be necessary (there was no warning that I should not). When I started
| getting the problems, I checked the web and discovered that Networkdays() was
| now a native function. I removed the TPA but the problems persist. I'm
| thinking now that I should uninstall and reinstall Office 2007.
|
| You say you haven't found a workaround - does that mean that you have also
| experienced the problem?
|
| Sean
|
|
|
| "iliace" wrote:
|
| > This will happen with formulas using Analysis ToolPak functions,
| > because those are now built-in Excel functions without an add-in. I
| > haven't found a workaround, but then again, I hardly used ATP
| > worksheet functions prior to 2007.
| >
| >
| > > I have also just migrated to Excel 2007 and am experiencing the same
| > > problem. Any formula using Networkdays() is replaced with =#N/A. These
| > > formulae always worked in 2003 but now result in a "File Error: Data may have
| > > been lost" message when I open the file in 2007, along with all the
| > > Networkdays() formulae being replaced with =#N/A. There are several threads
| > > on this topic, but so far no answers.
| > >
| > > Sean
| > >
| > > "Stan Brown" wrote:
| > > > Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith
| > > > <[email protected]>:
| > > > > when I opened the sheet this morning (in
| > > > > compatability mode initially), all the formulas had been replaced with =#N/A.
| > >
| > > > I suspect you have a #NA somewhere in a precedent cell. Either trace
| > > > the logic back through the cells and arrays that are used in your
| > > > formulas, or use the Formula Auditing feature.
| > >
| > > > --
| > > > Stan Brown, Oak Road Systems, Tompkins County, New York, USA
| > > > http://OakRoadSystems.com/
| > > > "If there's one thing I know, it's men. I ought to: it's
| > > > been my life work." -- Marie Dressler, in /Dinner at Eight/
| >
| >

 

[Sean]

Nieck;
Unfortunately, no. What is happening is that when I open the file, I get the
message: "File Error: Some Data may have been Lost" and when I look at the
offending cells I see the numbers (values) that should be there, i.e.
everything looks OK. But if I look at the formula in each cell, it has been
changed to: "=#N/A". After I press Ctrl-Alt-Shft-F9 all the visible values
change to "#N/A". So, although Ctrl-Alt-Shft-F9 doesn't fix it, I am glad you
mentioned it because it's a quicker way of finding where the problems are.
Sean