Possible BUG: IsEven() and IsOdd() give #VALUE error with dynamic array parameter

[jbwarren]

Why do IsEven() and IsOdd() give a #VALUE error when used with a dynamic array parameter?  E.g.:

A1: =Sequence(10)

B1: =IsEven(A1#)

C1: =IsEven(--A1#)

D1: =IsNumber(A1#)

In the example above, B1 gives a #VALUE error, while C1 works as expected.  (Using NumberValue() instead of "--" also works.)  Interestingly, D1 works as well.  In fact, all of the Is<x>() functions play nicely with dynamic arrays, with the exception of IsEven() and IsOdd().*  This, plus the fact that they work nicely in conjunction with double negative and/or NumberValue() implies that this is a bug.  Is that correct?

* IsFormula() and IsRef() are unusual cases.  IsFormula(A1#) does output a dynamic array, although the result isn't what I would expect, and I can't think of a good reason it would be implemented that way, but so be it.  IsRef(A1#) outputs a scalar value of TRUE, which is somewhat understandable, although it would seem more consistent to have it output a dynamic array and instead have people use IsRef(A1) to output a scalar value.  On a somewhat related note, I wonder if there are plans for a version of Type() that will map to array elements and output a dynamic array?  Perhaps as an optional second parameter?

Thanks,

-Jeremy

[#2]

Also interestingly, IsEven({1, 2, 3, 4}) works, as does IsEven(Sequence(4)).  IsEven(A1:A4) gives a #VALUE error, while IsEven(--A1:A4) works.

It seems like IsEven() and IsOdd() work with arrays, but not with references to arrays or ranges.  As mentioned above, all of the other Is<X>() functions work with references to dynamic arrays or with ranges.  This definitely seems like a bug, or at least a completely unnecessary functional inconsistency if this is somehow the desired behavior.

Thanks,

-Jeremy

[#3]

@jbwarren 

Jeremy, not only. For example =EOMONTH(TODAY(),A1:A4) returns an error and =EOMONTH(TODAY(),+A1:A4) returns the spill. Some other functions also do not work with ranges and arrays in a same way.

We may consider that as a  bug since we expect bit different functionality. Or we may consider that as a different behaviour, specific for different functions.

In any case I don't think old functions will be re-writing to make them work exactly the same way with dynamic arrays. It take years and introduces a lot of new bugs. Is not worth.

[#4]

@jbwarren 

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

[#5]

@Detlef Lewin 

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

[#6]

@jbwarren 

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.

[#7]

@Detlef Lewin 

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(), ...