Remember Me?

#1
November 23rd 07, 07:40 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 28
Formula replaced with =#N/A in 2007 - why?

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(\$U6BD\$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,\$V6BD\$2),S UM(\$AO6*(NETWORKDAYS(BD\$1,BD\$2)/NETWORKDAYS(\$U6,\$V6))),\$AO6)))))

Whe
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?

Alan

#2
November 25th 07, 01:48 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Mar 2007 Posts: 524
Formula replaced with =#N/A in 2007 - why?

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
"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/
#3
January 23rd 08, 01:30 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 208
Formula replaced with =#N/A in 2007 - why?

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
:
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
"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/

#4
January 23rd 08, 01:42 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 2,480
Formula replaced with =#N/A in 2007 - why?

Hi

Press the round office buttonExcel OptionsAddinsGocheck Analysis
ToolpakOK

--

Regards
Roger Govier

"Sean" wrote in message
...
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
on this topic, but so far no answers.

Sean

"Stan Brown" wrote:

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
"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/

#5
January 23rd 08, 05:15 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 229
Formula replaced with =#N/A in 2007 - why?

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.

On Jan 22, 8:30*pm, Sean wrote:
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
:
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/

#6
January 23rd 08, 01:16 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 208
Formula replaced with =#N/A in 2007 - why?

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

"Roger Govier" wrote:

Hi

Press the round office buttonExcel OptionsAddinsGocheck Analysis
ToolpakOK

--

Regards
Roger Govier

"Sean" wrote in message
...
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
on this topic, but so far no answers.

Sean

"Stan Brown" wrote:

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
"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/

#7
January 23rd 08, 01:21 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 208
Formula replaced with =#N/A in 2007 - why?

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.

On Jan 22, 8:30 pm, Sean wrote:
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
:
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
"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/

#8
January 23rd 08, 01:30 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 3,440
Formula replaced with =#N/A in 2007 - why?

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Sean" wrote in message news | 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.
|
|
| On Jan 22, 8:30 pm, Sean wrote:
| 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
| :
| 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
| "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/
|
|

#9
January 23rd 08, 01:56 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 208
Formula replaced with =#N/A in 2007 - why?

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

"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Sean" wrote in message news | 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.
|
|
| On Jan 22, 8:30 pm, Sean wrote:
| 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
| :
| 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
| "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/
|
|

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post T Excel Discussion (Misc queries) 2 June 28th 07 04:52 PM BCB Excel Discussion (Misc queries) 4 August 4th 06 06:29 AM Lorissa New Users to Excel 2 February 24th 06 04:57 PM jay_babcock Excel Discussion (Misc queries) 4 October 14th 05 10:28 PM Sheda Excel Discussion (Misc queries) 2 January 16th 05 03:23 PM

All times are GMT +1. The time now is 02:02 PM.