Forum Discussion
Possible BUG: IsEven() and IsOdd() give #VALUE error with dynamic array parameter
Trying to find an explanation.
IS functions states every IS function except ISODD() and ISEVEN(). And is states that a "value" is required.
ISODD() on the other hand requires a "number".
I get that IsEven() and IsOdd() require input values to be numbers, while IsNumber() & other IS functions don't. But the input parameter is an array of numbers. IsNumber(A1#) returns an array of TRUE values. More to the point, IsEven(Sequence(10)) works, while IsEven(A1#) doesn't. And IsEven(+A1#) does! This is definitely a bug. There's something about a reference to a dynamic array that "poisons" the input for IsEven()/IsOdd(), where giving it the underlying dynamic array directly and/or "cleaning" it with any other function ("+" or "--") works.
So IsEven() and IsOdd() can handle dynamic arrays just fine, but not through a direct reference. That's not right - completely anathema to how Excel works. The entire product is about composing formulas by referencing other cells (which contain formulas)!
-Jeremy
Based on the support pages I suppose that ISODD()/ISEVEN() are programmed differently then the other IS functions.
ISEVEN() expects a number or a range of numbers. That is why +A1# or SEQUENCE() inside ISEVEN() work. However A1# is not recognized as a (spilled) range. Not really a bug because ISEVEN() was there before spilled ranges but definitely an inconsistency that should be fixed by Microsoft.
Potato potato - agreed. 🙂 I'm not aware of any other function where F(expr) works but F(ref/range), cell/range=expr doesn't, with the possible exception of functions explicitly working on type/cell metadata - e.g., ROW(), TYPE(), ...