Formula evaluates to #NAME!

[Biff]

Hi Folks!

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

 

[Ken Wright]

Hmmm - I tried it in cell A2 on a sheet and got exactly the same as you, so then
tried the individual elements and had no problem. I then put the first
NETWORKDAYS part in another cell A3 and referred the original formula to that
cell, eg:-

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

 

[Biff]

Hi Ken!

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

 

[Ken Wright]

LOL - Happy to send you a working example at my end to see if it works when it
gets to your end. Works for me in both 2002 and 2003.

 

[Biff]

OK, sending you an email.

Thanks
Biff

-----Original Message-----
LOL - Happy to send you a working example at my end to see if it works when it
gets to your end. Works for me in both 2002 and 2003.

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

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






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


.

 

[Jonathan Rynd]

but when I
use the menu command, Evaluate Formula, it evaluates
straight through to #NAME!

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.

 

[Biff]

Hi Jonathan!

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

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

Biff

 

[Jonathan Rynd]

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

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

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.

 

[Harlan Grove]

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 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.

 

[Ken Wright]

Hi Harlan - it's got me confused as hell, because I did actually manage to get
it working by playing about with it as I noted yesterday, and I then checked it
was working in both 2002 and 2003, and did it a number of times in each just to
be sure. BUT, I wake up this morning, see that Biff wanted me to send him my
file and thought I'll just check it once again before I send it and lo and
behold it no longer works, and nothing I'm doing at the moment seems to change
that. :-(

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

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

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 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.

 

[Aladin Akyurek]

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.

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.

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 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.

 

[Ken Wright]

OK, still no idea why, but at least I managed to recreate what I did:-

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

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

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

Hi Harlan - it's got me confused as hell, because I did actually manage to get
it working by playing about with it as I noted yesterday, and I then checked it
was working in both 2002 and 2003, and did it a number of times in each just to
be sure. BUT, I wake up this morning, see that Biff wanted me to send him my
file and thought I'll just check it once again before I send it and lo and
behold it no longer works, and nothing I'm doing at the moment seems to change
that. :-(

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

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

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 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.

 

[Ken Wright]

Technical conclusion ( if only to snip the post :-> ) - Something in Excel 2003
is screwed wrt this. <g>

 

[hgrove]

Biff wrote...
...

So, in conclusion, I'll just chalk this up to one of Excel

mysteries!
...

Not a mystery, a feature