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