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
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
Press the round office button>Excel Options>Addins>Go>check Analysis
Toolpak>OK
--
Regards
Roger Govier
"Sean" <Se...@discussions.microsoft.com> wrote in message
news:2A0AC59B-3D68-4201...@microsoft.com...
On Jan 22, 8:30 pm, Sean <S...@discussions.microsoft.com> 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
> > <AlanSm...@discussions.microsoft.com>:
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
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Sean" <Se...@discussions.microsoft.com> wrote in message news:D3B4ECC3-92C4-4267...@microsoft.com...