#NAME?

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

Hello Dean,

Pressing F2 places Excel in Edit mode. You must exit Edit mode before attempting another action, like recalculating the worksheet with F9. Make your changes to the cell, click the check mark in the Formula Bar and then proceed with your testing.

Hope this helps,
Leith Ross

Dean

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

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-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, ****?

Dean

"**** Kusleika" <[email protected]> wrote in message
news:[email protected]...
> Dean
>
> Is it showing a non-zero value or the #NAME? error?
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-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.


--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-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

"**** Kusleika" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-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. "**** Kusleika" <[email protected]> wrote in message news:[email protected]...
> 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.
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-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.*****-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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com