I have an iserror formula i.e.,

if(iserror(a1),0,a1)

that is showing a non-zero value, one which I completely believe is correct.
However, when I go to the cell, hit F2, then F9, it shows #NAME?.

I also tried F2, then ctrl alt F9, but still got the same #NAME? result.

Why does this happen?

Thanks,
Dean

Dean

Is it showing a non-zero value or the #NAME? error?

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

Dean wrote:
I have an iserror formula i.e.,

if(iserror(a1),0,a1)

that is showing a non-zero value, one which I completely believe is
correct. However, when I go to the cell, hit F2, then F9, it shows
#NAME?.
I also tried F2, then ctrl alt F9, but still got the same #NAME?
result.
Why does this happen?

Thanks,
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?.

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.

What do you think, Dick?

Dean

"Dick Kusleika" wrote in message
...
Dean

Is it showing a non-zero value or the #NAME? error?

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

Dean wrote:
I have an iserror formula i.e.,

if(iserror(a1),0,a1)

that is showing a non-zero value, one which I completely believe is
correct. However, when I go to the cell, hit F2, then F9, it shows
#NAME?.
I also tried F2, then ctrl alt F9, but still got the same #NAME?
result.
Why does this happen?

Thanks,
Dean

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

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

Dean

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.

No need. Based on what you said, I was able to reproduce 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.

From the limited testing I did, it appears that you cannot calculate, in the
formula bar, a formula with two or more ATP functions. You can calc this

=IF(ISERROR(XIRR($B$3:$B$6,$A$3:$A$6,0.1)),"Err"," not err")

but you can't calc this

=XIRR($B$3:$B$6,$A$3:$A$6,0.1)+XIRR($B$3:$B$6,$A$3 :$A$6,0.1)

nor can you calc these

=MROUND(1.3,0.5)+MROUND(2.3,0.5)
=RANDBETWEEN(1,10)+RANDBETWEEN(1,10)
=ISEVEN(2)&ISEVEN(2)

I thought maybe this was any add-in, not just the ATP, but I tried it on a
function that I wrote that's in an add-in and it didn't work. It could be
that the ATP is a COM Add-in and that this holds true for all COM Add-ins, I
just don't have any other COM Add-ins at hand to test it. (By the way, if
this is true, that makes one of my predictions pretty damn close.)

I'll through this out on my blog and see if anyone can narrow down the
particulars. In the mean time, if you want to calc this type of formula in
the formula bar, you have to do it in a number of steps (equal to the number
of ATP functions you have). Highlight a portion of the function making sure
it only includes one ATP function and repeat until the whole thing is
calced. Kind of a pain, but I think you're stuck with it.

Nice bug find, by the way. I can't believe this has never come up before,
or at least that I could find.

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

<< Kind of a pain, but I think you're stuck with it. ... Nice bug find, by the way. I can't believe this has never come up before, or at least that I could find.

I'm not sure if you addressed the fact that the formula does produce an answer, except if you try to F2-F9 it (in which case, you get #NAME). But, I think the explanation might be over my head anyway, so don't worry. Really, I was more worried that the value produced might be unstable or corrupted or wrong and I think you are suggesting it is probably OK. And, if I am paranoid, I can break it up into steps, as you suggest.

Your finding that you can't add two such functions is certainly very interesting, even to a layman like me.

Lastly, thank you for the compliment. I am usually shunned when I point out EXCEL bugs. This was a first!

If you really enjoy finding and explaining bugs, here's an oldie but a goodie. Kindly look back at my Jan 25th post (11:48PM PST?) entitled "Scary". It has to do with a dependent cell not updating with a change in an independent variable cell's value. If I hit F9 or cntl alt F9, it still does not update. If I go into edit mode, take out a character and immediately put it back, and hit enter, it updates fine. Or, if I save the file, close the file, close EXCEL, and re-open the file, the update is there. This was a very basic file, as discussed in that thread. Let me know what you think, if you care to.

Dean

It's not that much of a pain. "Dick Kusleika" wrote in message ...
Dean

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.

No need. Based on what you said, I was able to reproduce 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.

From the limited testing I did, it appears that you cannot calculate, in the
formula bar, a formula with two or more ATP functions. You can calc this

=IF(ISERROR(XIRR($B$3:$B$6,$A$3:$A$6,0.1)),"Err"," not err")

but you can't calc this

=XIRR($B$3:$B$6,$A$3:$A$6,0.1)+XIRR($B$3:$B$6,$A$3 :$A$6,0.1)

nor can you calc these

=MROUND(1.3,0.5)+MROUND(2.3,0.5)
=RANDBETWEEN(1,10)+RANDBETWEEN(1,10)
=ISEVEN(2)&ISEVEN(2)

I thought maybe this was any add-in, not just the ATP, but I tried it on a
function that I wrote that's in an add-in and it didn't work. It could be
that the ATP is a COM Add-in and that this holds true for all COM Add-ins, I
just don't have any other COM Add-ins at hand to test it. (By the way, if
this is true, that makes one of my predictions pretty damn close.)

I'll through this out on my blog and see if anyone can narrow down the
particulars. In the mean time, if you want to calc this type of formula in
the formula bar, you have to do it in a number of steps (equal to the number
of ATP functions you have). Highlight a portion of the function making sure
it only includes one ATP function and repeat until the whole thing is
calced. Kind of a pain, but I think you're stuck with it.

Nice bug find, by the way. I can't believe this has never come up before,
or at least that I could find.

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

Dean wrote:
<< Kind of a pain, but I think you're stuck with it. ... Nice bug
find, by the way. I can't believe this has never come up before, or
at least that I could find.

I'm not sure if you addressed the fact that the formula does produce
an answer, except if you try to F2-F9 it (in which case, you get
#NAME).

That's a given, so I didn't think it was necessary. Here's a plug for my
website
http://www.dicks-blog.com/archives/2...e-formula-bar/


But, I think the explanation might be over my head anyway,
so don't worry. Really, I was more worried that the value produced
might be unstable or corrupted or wrong and I think you are
suggesting it is probably OK. And, if I am paranoid, I can break it
up into steps, as you suggest.

I personally wouldn't worry that the calculated value is incorrect or
unstable, but I didn't want to make that decision for you. I think this is
an example of a certain algorithm (calculating values in the formula bar)
that doesn't work well in certain situations (calling functions from an XLL
twice) and that it doesn't have anything to do with the integrity of the
calculation outside of the formula bar. However...

If someone said, "Well, they screwed this up so who knows what else they
screwed up.", I don't have a good argument against that. In short, only you
can decide if you need to worry. If you're working for NASA, I think you
should worry. If you're doing your mother-in-laws taxes, don't fret.


Your finding that you can't add two such functions is certainly very
interesting, even to a layman like me.

Lastly, thank you for the compliment. I am usually shunned when I
point out EXCEL bugs. This was a first!

If you really enjoy finding and explaining bugs, here's an oldie but
a goodie. Kindly look back at my Jan 25th post (11:48PM PST?)
entitled "Scary". It has to do with a dependent cell not updating
with a change in an independent variable cell's value. If I hit F9
or cntl alt F9, it still does not update. If I go into edit mode,
take out a character and immediately put it back, and hit enter, it
updates fine. Or, if I save the file, close the file, close EXCEL,
and re-open the file, the update is there. This was a very basic
file, as discussed in that thread. Let me know what you think, if
you care to.


You're welcome and I will take a look.

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