Actually, I misstated. The formula is not complex. In fact, it's not a
formula at all. The iserror's argument is an EXCEL function (XIRR) which is
a complex one, in my experience. I tried the simplest example in a new
file, which I could send, and it still had the problem.

But perhaps the answer is that you shouldn't use ANY EXCEL function as the
argument of ISERROR. Could that be right? Actually, no, I used a simpler
function and did not have that problem. Perhaps, it's just certain more
difficult functions. XIRR probably has to internally iterate to find a
solution. It does seem to still work, mind you. It's just that when you
try to look at the value in edit mode, as I've tried to describe earlier, it
returns #NAME.

Does that help? I really admire your willingness to dig in, by the way!

Dean

"Dick Kusleika" wrote in message
...
Dean:

I guess I wasn't very clear. The cell computes to a number and that
number appears in, say, two decimal places on the spreadsheet,
subject to the two-decimal place formatting of that cell. Suppose I
was just curious what the value was shown to the maximum number of
decimal places EXCEL actually uses. Then I might go to the cell, hit
the F2 key, then the F9 key. Then once I copied down the result on a
piece of paper, I would hit the escape key so the formula doesn't get
wiped out. This is what I have done. When I hit escape, the formula
and value is, of course, still there in the cell.
What I am saying is that, after hitting F9 (or cntl-alt-F9, which
I've been told does a more complete recalc), the value that appears
up in the formula bar area is #NAME?.


Aha, I get it now.

Ok, cell A1 in my example below is really a very complex formula, not
something simple. But that shouldn't matter - should it? I just
fear that I cannot trust a worksheet that exhibits this kind of
behavior.

At this point, I don't know if it should matter. There's obviously
something going on in your formula that's causing this. It's not
something I've heard of before, but I think it will be fairly easy to
track down. If we can reproduce it, we may learn something.
Unfortunately that "something" may be a bug, which won't do much for our
trust factor. If we can't reproduce it, I'll end up blaming it on
workbook corruption, because that's what I do when I can't reproduce it.

First, I'll make a prediction or two. Your formula is some unreasonably
long formula and Excel can only calculate up to some limit. So you have
more than 1,024 characters in your formula, but Excel can't calculate
beyond that limit and it ends up splitting some function name in half
which it then can't recognize. This is purely speculation, so believe
there's any truth to it. Yet.

Or, you're using, in your formula, a function from a COM Add-in and Excel
can't calculate that function name when the cell's in edit mode and
therefore thinks it's an undefined range name. Again, pure guess.

Here's how you start to narrow it down. When you hit F9, it calcs the
whole formula. If you were to select a portion of the formula and hit F9,
it would only calculate that portion. The portion that you highlight has
to be valid - the parentheses have to match, for instance. So start
highlighting small portions of your formula and F9'ing them (I use Cntl+=,
but that's just a preference). If you get to a portion that returns
#NAME?, you've narrowed it down and you can narrow it down further. If
you end up calcing the whole formula without getting the error, then my
first prediciton starts to look good.

If you can share the formula, that would be best. If not, just start
F9ing small portions of it and see what you can see.


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com