NETWORKDAYS gives #NAME even though Analysis Toolpak is installed

[Stan Brown]

This is a real head-scratcher in Excel 2003 under Win XP Pro SP2.

My formula is
=MIN(ProfWeeks,MAX(0,NETWORKDAYS(E7,TODAY())))
and it gives #NAME?. I clicked on the little ! icon and chose Show
Calculation Steps; it confirms that NETWORKDAYS is the problem.

Yes, ATP is checked in "Tools -> Addins".

http://support.microsoft.com/default.aspx?scid=kb;en-us;291058 gives
advice, which I followed, to force a recalculate. That didn't help
either.

Help!

 

[Guest]

Hi
Is VBA ATP ticked in the add-ins too? Maybe that function is part of the VBA
bit.

Andy.

 

[Dave Peterson]

I think I'd try turning the analysis toolpak addin off, then turn it on to see
if that helped.

If it didn't, I think I'd reinstall that addin (maybe even remove it first).

 

[Stan Brown]

I think I'd try turning the analysis toolpak addin off, then turn it on to see
if that helped.

If it didn't, I think I'd reinstall that addin (maybe even remove it first).

Thanks for the response. I did try unticking it, clicking OK, then
ticking it and clicking OK. Then I tried a MS office repair
installation, then with Add/Remove I deleted all the Add-ins, then
re-installed them and re-ticked ATP and clicked OK.

I'm really baffled. I've tried Googling, but aside from the
reference I cited above the others seemed to be cured by ticking the
Data Analysis add-in.

I'm wondering if this is some weird permission issue (though it
shouldn't be, since my disk is FAT32) or registry issue. I did the
installation as Admin and was running as limited user; however when
I tried the spreadsheet as Admin I still had the #NAME? problem.

Here are the files in my addins directory:

Directory of C:\Program Files\MSOffice\OFFICE11\ADDINS\*

1998-08-03 17:28 623 ______N______ DLGSETP.ECF
1998-08-03 17:28 808 ______N______ DUMPSTER.ECF
1998-07-31 11:01 830 ______N______ FAXEXT.ECF
2003-07-10 1:23 7,168 ______N______ MSOSEC.DLL
2003-03-18 15:45 179 ______N______ MSOSEC.XML
1998-10-07 23:46 778 ______N______ MSSPC.ECF
2003-03-18 15:23 344,064 ______N______ MSVCR71.DLL
2003-07-10 1:11 77,824 ______N______ OTKLOADR.DLL
1998-09-02 16:22 1,936 ______N______ OUTEX.ECF
2000-07-20 10:04 862 ______N______ OUTEX2.ECF
2003-07-23 22:32 45,112 ______N______ OUTLVBA.DLL
1998-10-07 23:47 626 ______N______ PMAILEXT.ECF
1998-08-03 17:28 693 ______N______ SCRPTXTN.ECF

 

[Art®]

For what it's worth, I tried your formula and got a similar error. But, when
I tried using a simpler formula with NETWORKDAYS, it worked just fine.

Why not try =NETWORKDAYS(E7,E8) or something similar. Make sure that E7 and
E8 contain valid dates. If the formula works, then at least you'll know it
isn't the addin that's messing things up.

Art

 

[Peo Sjoblom]

Do you get an error with this formula

=WORKDAY(1,5)

if so then ATP cannot be installed correctly, uncheck it and close excel,
try again by checking it. If it is not working, plop in the CD and install
ATP from there again. You can also search for it FUNCRES.xla, you can use the
browse button from tools>add-ins and select it from there (after finding it
doing a file search)

Regards,

Peo Sjoblom

 

[Stan Brown]

For what it's worth, I tried your formula and got a similar error. But, when
I tried using a simpler formula with NETWORKDAYS, it worked just fine.

Why not try =NETWORKDAYS(E7,E8) or something similar. Make sure that E7 and
E8 contain valid dates. If the formula works, then at least you'll know it
isn't the addin that's messing things up.

Thanks for responding.

I put today() in A1, 2005-05-01 in A2, =NETWORKDAYS(a2,a1) in A3.
Got #NAME? again.

 

[Stan Brown]

Do you get an error with this formula

=WORKDAY(1,5)

Yes, I do: #NAME?.

if so then ATP cannot be installed correctly, uncheck it and close excel,
try again by checking it.

I tried that before, but following your message I tried it again.
Didn't help.

If it is not working, plop in the CD and install
ATP from there again.

SETUP thinks that it's already installed. I tried uninstalling it
and then reinstalling it as I said earlier, but it didn't help.

You can also search for it FUNCRES.xla, you can use the
browse button from tools>add-ins and select it from there (after finding it
doing a file search)

Did that -- it was in C:\Program Files\MSOffice\Library\Addins but
after using Tools -> Addins -> Browse I still get the #NAME? error
with =NETWORKDAYS.

Help! I've done everything right as far as I can see, but it's just
not working.

The only other thing I can thing to mention is that when I click
"Browse" in Tools->Addins it starts _not_ in the above Program Files
directory but in
C:\Documents and Settings\{user}\Application Data\Microsoft\Addins
Could that make a difference, and if so what can I do to correct it?

 

[Peo Sjoblom]

It should be in

C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis

can you see if you have that folder, if so uncheck it under tools>add-ins,
close excel,
move FUNCRES.xla into the above path/folder, restart excel. If you don't get
any error messages check it and see if it works. If you get an error
message, plop in the CD, select add or remove features, next window select
advanced, find excel>add-ins and remove it. When you are done,click
start>run>regedit,
press ctrl + f and search for FUNCRES.xla and delete the path that's in
there, press F3 to search for it and delete each instance of funcres.xla
(there might be one or two). Now restart excel and check that it works OK
(don't install it), make a search for funcres.xla make sure you can't find
it. Now repeat and go into install again but this time check Analysis
ToolPak under advanced installation, if it uninstalled it should be a red x
marking that it isn't available, select run from computer, now it should be
installing.
Start Excel, go into tools>add-ins and check it, that should do it.

I once did this on a friends computer and I made it work, he had removed the
add-in and then he put it in the wrong folder, then he got it working except
that he got this error message that it couldn't find it every time he
started it. The only way I was able to make it work was to remove the path
string from the registry, delete it using the Office CD and reinstall it
using the Office CD

Note that if you feel uncomfortable to mess around with the registry, don't
do it.If you do mistakes it can cause windows not to work properly so you do
it at your own risk. I do it all the time but that doesn't mean I will
guarantee it will work

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Stan Brown]

It should be in
C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis
can you see if you have that folder, if so uncheck it under tools>add-ins,
close excel,
move FUNCRES.xla into the above path/folder, restart excel.

Thanks for responding. Yes, FUNCRES.XLA is in the above folder.
(Sorry, in my previous message I said "Addins" instead of
"Analysis".)

Sorry -- I'm confused about your next instruction. You say if it's
there then I should mope it into the folder where it already is??
I'm guessing you mean if it's _not_ there.

I've already tried uninstalling and reinstalling the add-in.

Following your suggestion I looked in the Registry and found two
items with paths to FUNCRES.XLA; both had the correct path in the
string data.

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options\OPEN
and
HKEY_USERS\{lots of digits}\
Software\Microsoft\Office\11.0\Excel\Options

I once did this on a friends computer and I made it work, he had removed the
add-in and then he put it in the wrong folder, then he got it working except
that he got this error message that it couldn't find it every time he
started it.

I don't get any error message when Excel starts up. The only symptom
is #NAME? when I try to use NETWORKDAYS or other functions that the
help file says are in the Analysis Toolpak.

The only way I was able to make it work was to remove the path
string from the registry, delete it using the Office CD and reinstall it
using the Office CD

I mess with the registry all the time, but before I do this let me
make sure I understand what you're suggesting. If I understand you,
you're saying that even if FUNCRES.XLA is present in the above-
mentioned directory _and_ the Registry paths are correct, I should
still
1. Delete the above two registry keys
2. Add/Remove and uninstall the Analysis Toolpak
3. Start Excel and verify that it's not even listed in the
Add-ins.
4. Do another Add/Remove and reinstall ATP.
5. Go into Excel and tick the ATP.

I tried numbers 2 through 5 before posting my original article, but
if you're telling me to do 1 through 5 in sequence I'm willing.

 

[Peo Sjoblom]

I mess with the registry all the time, but before I do this let me

make sure I understand what you're suggesting. If I understand you,
you're saying that even if FUNCRES.XLA is present in the above-
mentioned directory _and_ the Registry paths are correct, I should
still
1. Delete the above two registry keys
2. Add/Remove and uninstall the Analysis Toolpak
3. Start Excel and verify that it's not even listed in the
Add-ins.
4. Do another Add/Remove and reinstall ATP.
5. Go into Excel and tick the ATP.

I tried numbers 2 through 5 before posting my original article, but
if you're telling me to do 1 through 5 in sequence I'm willing.

I was able to get it working by doing

2.
1.
3.
4.
5.

using your numbers from above, also before starting excel (after
uninstalling it) go into the library folder
where it was located and make sure it isn't there, I believe the whole
analysis folder should be gone


--
Regards,

Peo Sjoblom

(No private emails please)

 

[Stan Brown]

I was able to get it working by doing

2.
1.
3.
4.
5.

using your numbers from above, also before starting excel (after
uninstalling it) go into the library folder
where it was located and make sure it isn't there, I believe the whole
analysis folder should be gone

Thanks for the reply -- I'll try it exactly as you suggest, and I'll
report back!

 

[Stan Brown]

I was able to get it working by doing

2.
1.
3.
4.
5.

I followed the steps in the order you said. After (2), only one key
was left in the Registry, and I deleted it., As you suggested, the
entire Analysis folder was gone. (3) was fine, and I checked that
FUNCRES.XLA was back in C:\Program Files\MSOffice\Library\Analysis.
(4) went fine, and (5) again appeared to work. I exited Excel and
restarted it, and verified that the ATP was still ticked. However,
when I tried another =NETWORKDAYS(D5,D6), with two dates in those
cells, I again got #NAME?.

Any other ideas? I'm completely stumped.

<rant>
I'm really annoyed at Office 2003. Every application I use has
annoying bugs. Excel says it installs things but apparently it
doesn't actually install them. Access changes the settings on the
default printer when I click the little printer icon. Word breaks
the Alt-nnnn key inputs, and when I select text it highlights a menu
selection so that the Ctrl-C or Ctrl-X key doesn't work.
</rant>

 

[Peo Sjoblom]

I followed the steps in the order you said. After (2), only one key

was left in the Registry, and I deleted it., As you suggested, the
entire Analysis folder was gone. (3) was fine, and I checked that
FUNCRES.XLA was back in C:\Program Files\MSOffice\Library\Analysis.
(4) went fine, and (5) again appeared to work. I exited Excel and
restarted it, and verified that the ATP was still ticked. However,
when I tried another =NETWORKDAYS(D5,D6), with two dates in those
cells, I again got #NAME?.

Any other ideas? I'm completely stumped.

<rant>
I'm really annoyed at Office 2003. Every application I use has
annoying bugs. Excel says it installs things but apparently it
doesn't actually install them. Access changes the settings on the
default printer when I click the little printer icon. Word breaks
the Alt-nnnn key inputs, and when I select text it highlights a menu
selection so that the Ctrl-C or Ctrl-X key doesn't work.
</rant>

Sorry Stan, I am stumped as well. No more ideas, all I can say is that it
worked after I did those steps
Given my nature I would probably uninstall Office and remove every ounce of
it either manually by searching the registry or see if there is help
programs at MS site that remove every part of it

Meanwhile here's a NETWORKDAYS workaround using built in functions

=((A2-A1)+1)-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)),2)>5))-SUMPRODUCT(--(F2:F12>=A1),--(F2:F12<=A2),--(WEEKDAY(F2:F12)<>1))

where A1 is the start and A2 the end date, F2:F12 is a range with public
holidays, the networkdays would look like

=NETWORKDAYS(A1,A2,F2:F12)



Regards,

Peo Sjoblom

 

[Stan Brown]

Sorry Stan, I am stumped as well. No more ideas, all I can say is that it
worked after I did those steps
Given my nature I would probably uninstall Office and remove every ounce of
it either manually by searching the registry or see if there is help
programs at MS site that remove every part of it

Thanks for your reply. My thinking has been running the same as
yours: uninstall that sucker, clean the registry manually, reinstall
and hope for the best. (I have a sneaking suspicion that by
installing it to MSOffice instead of Microsoft Office I may have
uncovered a bug in the add-in process.)

Thanks also for the workaround formula. I had evolved one of my own,
but I didn't know about INDIRECT() and so mine was much nastier.
Thanks for the new learning!

 

[Stan Brown]

This is a real head-scratcher in Excel 2003 under Win XP Pro SP2.

My formula is
=MIN(ProfWeeks,MAX(0,NETWORKDAYS(E7,TODAY())))
and it gives #NAME?. I clicked on the little ! icon and chose Show
Calculation Steps; it confirms that NETWORKDAYS is the problem.

Yes, ATP is checked in "Tools -> Addins".

http://support.microsoft.com/default.aspx?scid=kb;en-us;291058 gives
advice, which I followed, to force a recalculate. That didn't help
either.

I don't know if this is true for earlier versions, but in Excel 2003
there are TWO check boxes: "Analysis Toolpak" and "Analysis Toolpak
VBA".

Excel 2003 help and Microsoft's site mention only the first, and I
had it checked, but I didn't have "Data Analysis" in the Tools menu,
and I didn't have functions like NETWORKDAYS().

Today, on a whim I also checked "Analysis Toolpak VBA", and now
I have Tools -> Data Analysis and a quick test of NETWORKDAYS worked
fine.

Those who were trying to help might have earlier versions of Excel
with only the one checkbox, or might have assumed when I checked
"Analysis Toolpak" that I checked both. But I'm pretty
literal-minded and since I wasn't told to check the VBA one I
didn't.

Anyway, I thought Peo and others might appreciate knowing that the
mystery is cleared up --and again, I'm grateful for the time you
spent with my problem.