Formula Error

[Domenic]

The following formula should return 4...

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"
No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

When the formula is entered in a Windows version of Excel, it returns
the correct answer. However, when I enter the formula in my Mac version
of Excel (Excel X 10.1.5), it returns 0 and I get the following error
message...

"Microsoft Excel cannot calculate the formula."

Does any one else with a Mac version of Excel get the same error
message? If so, which version are you using? Anyone get the correct
answer? Again, if so, which version?

 

[Peo Sjoblom]

I get a circular error with Excel X
all windows version from Excel 95 onwards work, looks like you found a bug

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Domenic]

Thanks for confirming this for me, Peo! I'll be bringing it to
Microsoft's attention tomorrow.

Thanks again, much appreciated!

 

[JE McGimpsey]

The following formula should return 4...

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"
No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

When the formula is entered in a Windows version of Excel, it returns
the correct answer. However, when I enter the formula in my Mac version
of Excel (Excel X 10.1.5), it returns 0 and I get the following error
message...

"Microsoft Excel cannot calculate the formula."

Does any one else with a Mac version of Excel get the same error
message? If so, which version are you using? Anyone get the correct
answer? Again, if so, which version?

Unfortunately, this is a known bug - I've reported it before, but it may
help to emphasize it. Any time the range/array has an error, SUMPRODUCT
gives a circular reference error.

 

[Domenic]

Thanks JE! I reported it to Microsoft this afternoon. As you said, it
may help to emphasize it.

They spent considerable time with me on the phone and confirmed that
there was a problem. I was told that someone would get back to me
within 72 hours.

Apparently, the problem still exists in the latest version, 2004.
That's disappointing. However, 2001 for the Mac OS 9 doesn't seem to
have that problem.

Thanks again, JE!

 

[Domenic]

I just finished talking with Microsoft about this problem and it seems
that, in fact, there is no bug. The fix is relatively simple...

Excel > Preferences > Calculation > and check 'Iteration'

That's it. Now the following formula will return the correct result...

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"
No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})

Also, the following formulas will now return the correct result when the
range/array contains an error value...

=LOOKUP(2,1/(A1:A100<>""),ROW(A1:A100))

and

{=MATCH(2,1/(A1:A100<>""))}

I don't quite understand how 'Iteration' affects the calculation, but
hey, the important thing is that is solves the problem.

 

[JE McGimpsey]

I just finished talking with Microsoft about this problem and it seems
that, in fact, there is no bug. The fix is relatively simple...

It's a bug.

Setting Iteration is a workaround that fixes the symptom (and I'd
forgotten that, thanks!), but it's a global setting, so it can affect
other calculations, including any that use circular references or
successive approximations.

It also limits Goal Seek.

There are NO inherent circular references in those formulae. It's a flaw
in the function's implementation. One shouldn't have to reduce the
functionality of the worksheet in order to use them.

 

[Domenic]

It's a bug.

Just when I was about to start celebrating...

Setting Iteration is a workaround that fixes the symptom (and I'd
forgotten that, thanks!), but it's a global setting, so it can affect
other calculations, including any that use circular references or
successive approximations.

It also limits Goal Seek.

I didn't realize that.

There are NO inherent circular references in those formulae. It's a flaw
in the function's implementation. One shouldn't have to reduce the
functionality of the worksheet in order to use them.

I agree. Thanks JE!

 

[Gomberg, Jeffrey G.]

... It's a flaw

Au contraire... It's a "feature"...

;-)

___________________________________________