Formula evaluates to #NAME!

I was working on a solution to a post and playing around
with some formulas. I do have the ATP installed and this
formula returns the *correct value in the cell* but when I
use the menu command, Evaluate Formula, it evaluates
straight through to #NAME!

=IF(NETWORKDAYS(A1,C1)>1,NETWORKDAYS(A1,C1)-NETWORKDAYS
(A1,C1)*5/24)

It's a pretty simple formula. When I use the step
evaluation process of F9 each individual expression does
evaluate properly but when the entire formula is evaluated
as a whole, once again it evaluates to #NAME!

If I change it to something like this:

=IF(NETWORKDAYS(A1,C1)>1,"Yes","No")

It evaluates properly.

Can anyone explain why this is happening?

Thanks
Biff

cell A3 --> =NETWORKDAYS(A1,C1)

cell A2 --> =IF(A3>1,NETWORKDAYS(A1,C1)-NETWORKDAYS(A1,C1)*5/24)

I then tried Evaluate on cell A2 again, and this now worked. Not that it should
make any difference, but I now copied and pasted the formula out of cell A3 back
into the formula in A2 to give me the original:-

=IF(NETWORKDAYS(A1,C1)>1,NETWORKDAYS(A1,C1)-NETWORKDAYS(A1,C1)*5/24)

This also now works fine using Evaluate formula - Go figure???

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Biff" <biffi...@comcast.net> wrote in message
news:3adf01c47289$a7c88980$a401...@phx.gbl...

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.726 / Virus Database: 481 - Release Date: 23/07/2004

Hmmm .... and go figure is right!

Well, I tried everything you did and it made no difference
at all!

When I made the first expression a reference:

IF(B13>1 .......

That even evaluated to #NAME!

Oh well!

Thanks
Biff

>.
>

news:3c9501c472a7$060bd480$a301...@phx.gbl...

Thanks
Biff

>.
>

Using F9 or Evaluate Formula causes the formula to be entered as an array
formula. Some of the functions don't support being entered as an array
formula. That probably explains the behavior you're seeing.

If that's the case, then why doesn't this formula exhibit
the same symptoms?

Biff

>.
>

Because the call to NETWORKDAYS is only in the condition part of the IF.
The problems only occur when the call to NETWORKDAYS is the "THEN" part of
the IF or the "ELSE" part of the IF.

If you pressed control-shift-enter to enter the formula into the cell,
you'd get a result that agreed with F9.

If this bit of supposition were true, then entering the formula

=IF(NETWORKDAYS(A1,C1)>1,NETWORKDAYS(A1,C1)-NETWORKDAYS(A1,C1)*5/24)

as an array formula would result in an error, no? Presumably a #NAME? error?
On my system entering this formula as an array formula returns the same
result as entering it as a nonarry formula.

So which functions are they that you believe can't be entered in array
formulas?

I have no certainty about the underlying cause of the partial evaluation
errors, but I'm pretty sure that any and all functions can be used in array
formulas. Actually, with regard to the former, some light testing makes it
appear that the formula evaluator (my nickname for whatever portion of the
Excel binary evaluates parts of formulas in the formula bar, as opposed to
the formula parser that encodes entered formulas into cells) can handle
expressions with only one add-in function at a time. That is,

=ISODD(1) [F2],[F9] => TRUE

=ISODD(1)+ISODD(2) [F2],[F9] => #NAME?

=ISODD(1)+SUM(0) [F2],[F9] => 1

If you're going to make conjectures (the polite term for wild-ass guesses),
be honest enough to state that they're just conjectures. At the very least
that'd prevent responses like this.

"Harlan Grove" <hrl...@aol.com> wrote in message
news:uTuocQtc...@TK2MSFTNGP12.phx.gbl...

Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004

A constant experience with formulas containing multiple functions from the
morefunc.xll add-in. F9 definitely does not possess the full functionality
of Excel's formula parser.

Open file in 2002, edit formula either by F2 or just click into it and then hit
Enter. Evaluate seemingly now works as many times as I care to try it. I save
file and close and then reopen in Excel 2003 and as long as I do not edit that
formula in any way (or even just enter edit mode and then hit enter), it works.
The second I hit edit mode though and then exit via hitting enter it dies until
i go round the loop via 2002 again. I close the file and reopen in Excel 2002
and first time round it dies - I hit edit mode again and then enter and it works
and so on?????????

"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:%23anDdHu...@tk2msftngp13.phx.gbl...

<snip>

I'm using 2002 and can't duplicate what you're doing. Are
you "editing" in the formula bar or in cell? But it
shouldn't matter.

So, in conclusion, I'll just chalk this up to one of
Excels mysteries!

Thanks to everyone for their input.

Biff

>.
>