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

%3CLINGO-SUB%20id%3D%22lingo-sub-1371714%22%20slang%3D%22en-US%22%3EPossible%20BUG%3A%20IsEven()%20and%20IsOdd()%20give%20%23VALUE%20error%20with%20dynamic%20array%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371714%22%20slang%3D%22en-US%22%3E%3CP%3EWhy%20do%20IsEven()%20and%20IsOdd()%20give%20a%20%23VALUE%20error%20when%20used%20with%20a%20dynamic%20array%20parameter%3F%26nbsp%3B%20E.g.%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EA1%3A%20%3DSequence(10)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EB1%3A%20%3DIsEven(A1%23)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EC1%3A%20%3DIsEven(--A1%23)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ED1%3A%20%3DIsNumber(A1%23)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20example%20above%2C%20B1%20gives%20a%20%23VALUE%20error%2C%20while%20C1%20works%20as%20expected.%26nbsp%3B%20(Using%20NumberValue()%20instead%20of%20%22--%22%20also%20works.)%26nbsp%3B%20Interestingly%2C%20D1%20works%20as%20well.%26nbsp%3B%20In%20fact%2C%20all%20of%20the%20Is%3CX%3E()%20functions%20play%20nicely%20with%20dynamic%20arrays%2C%20with%20the%20exception%20of%20IsEven()%20and%20IsOdd().*%26nbsp%3B%20This%2C%20plus%20the%20fact%20that%20they%20work%20nicely%20in%20conjunction%20with%20double%20negative%20and%2For%20NumberValue()%20implies%20that%20this%20is%20a%20bug.%26nbsp%3B%20Is%20that%20correct%3F%3C%2FX%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20IsFormula()%20and%20IsRef()%20are%20unusual%20cases.%26nbsp%3B%20IsFormula(A1%23)%20does%20output%20a%20dynamic%20array%2C%20although%20the%20result%20isn't%20what%20I%20would%20expect%2C%20and%20I%20can't%20think%20of%20a%20good%20reason%20it%20would%20be%20implemented%20that%20way%2C%20but%20so%20be%20it.%26nbsp%3B%20IsRef(A1%23)%20outputs%20a%20scalar%20value%20of%20TRUE%2C%20which%20is%20somewhat%20understandable%2C%20although%20it%20would%20seem%20more%20consistent%20to%20have%20it%20output%20a%20dynamic%20array%20and%20instead%20have%20people%20use%20IsRef(A1)%20to%20output%20a%20scalar%20value.%26nbsp%3B%20On%20a%20somewhat%20related%20note%2C%20I%20wonder%20if%20there%20are%20plans%20for%20a%20version%20of%20Type()%20that%20will%20map%20to%20array%20elements%20and%20output%20a%20dynamic%20array%3F%26nbsp%3B%20Perhaps%20as%20an%20optional%20second%20parameter%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E-Jeremy%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1371714%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1371733%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20BUG%3A%20IsEven()%20and%20IsOdd()%20give%20%23VALUE%20error%20with%20dynamic%20array%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371733%22%20slang%3D%22en-US%22%3E%3CP%3EAlso%20interestingly%2C%20IsEven(%7B1%2C%202%2C%203%2C%204%7D)%20works%2C%20as%20does%20IsEven(Sequence(4)).%26nbsp%3B%20IsEven(A1%3AA4)%20gives%20a%20%23VALUE%20error%2C%20while%20IsEven(--A1%3AA4)%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20like%20IsEven()%20and%20IsOdd()%20work%20with%20arrays%2C%20but%20not%20with%20%3CEM%3Ereferences%3C%2FEM%3E%20to%20arrays%20or%20ranges.%26nbsp%3B%20As%20mentioned%20above%2C%20all%20of%20the%20other%20Is%3CX%3E()%20functions%20work%20with%20references%20to%20dynamic%20arrays%20or%20with%20ranges.%26nbsp%3B%20This%20definitely%20seems%20like%20a%20bug%2C%20or%20at%20least%20a%20completely%20unnecessary%20functional%20inconsistency%20if%20this%20is%20somehow%20the%20desired%20behavior.%3C%2FX%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E-Jeremy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1371838%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20BUG%3A%20IsEven()%20and%20IsOdd()%20give%20%23VALUE%20error%20with%20dynamic%20array%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659949%22%20target%3D%22_blank%22%3E%40jbwarren%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJeremy%2C%20not%20only.%20For%20example%26nbsp%3B%3DEOMONTH(TODAY()%2CA1%3AA4)%20returns%20an%20error%20and%26nbsp%3B%3DEOMONTH(TODAY()%2C%2BA1%3AA4)%20returns%20the%20spill.%20Some%20other%20functions%20also%20do%20not%20work%20with%20ranges%20and%20arrays%20in%20a%20same%20way.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20may%20consider%20that%20as%20a%26nbsp%3B%20bug%20since%20we%20expect%20bit%20different%20functionality.%20Or%20we%20may%20consider%20that%20as%20a%20different%20behaviour%2C%20specific%20for%20different%20functions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20any%20case%20I%20don't%20think%20old%20functions%20will%20be%20re-writing%20to%20make%20them%20work%20exactly%20the%20same%20way%20with%20dynamic%20arrays.%20It%20take%20years%20and%20introduces%20a%20lot%20of%20new%20bugs.%20Is%20not%20worth.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1371871%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20BUG%3A%20IsEven()%20and%20IsOdd()%20give%20%23VALUE%20error%20with%20dynamic%20array%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659949%22%20target%3D%22_blank%22%3E%40jbwarren%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETrying%20to%20find%20an%20explanation.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FIS-functions-0F2D7971-6019-40A0-A171-F2D869135665%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EIS%20functions%3C%2FA%3E%26nbsp%3Bstates%20every%20IS%20function%20except%20ISODD()%20and%20ISEVEN().%20And%20is%20states%20that%20a%20%22value%22%20is%20required.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FISODD-function-1208A56D-4F10-4F44-A5FC-648CAFD6C07A%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EISODD()%3C%2FA%3E%26nbsp%3Bon%20the%20other%20hand%20requires%20a%20%22number%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372305%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20BUG%3A%20IsEven()%20and%20IsOdd()%20give%20%23VALUE%20error%20with%20dynamic%20array%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20that%20IsEven()%20and%20IsOdd()%20require%20input%20values%20to%20be%20numbers%2C%20while%20IsNumber()%20%26amp%3B%20other%20IS%20functions%20don't.%26nbsp%3B%20But%20the%20input%20parameter%20%3CEM%3Eis%3C%2FEM%3E%20an%20array%20of%20numbers.%26nbsp%3B%20IsNumber(A1%23)%20returns%20an%20array%20of%20TRUE%20values.%26nbsp%3B%20More%20to%20the%20point%2C%20IsEven(Sequence(10))%20works%2C%20while%20IsEven(A1%23)%20doesn't.%26nbsp%3B%20And%20IsEven(%2BA1%23)%20does!%26nbsp%3B%20This%20is%20definitely%20a%20bug.%26nbsp%3B%20There's%20something%20about%20a%20reference%20to%20a%20dynamic%20array%20that%20%22poisons%22%20the%20input%20for%20IsEven()%2FIsOdd()%2C%20where%20giving%20it%20the%20underlying%20dynamic%20array%20directly%20and%2For%20%22cleaning%22%20it%20with%20any%20other%20function%20(%22%2B%22%20or%20%22--%22)%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20IsEven()%20and%20IsOdd()%20can%20handle%20dynamic%20arrays%20just%20fine%2C%20but%20not%20through%20a%20direct%20reference.%26nbsp%3B%20That's%20not%20right%20-%20completely%20anathema%20to%20how%20Excel%20works.%26nbsp%3B%20The%20entire%20product%20is%20about%20composing%20formulas%20by%20referencing%20other%20cells%20(which%20contain%20formulas)!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-Jeremy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372539%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20BUG%3A%20IsEven()%20and%20IsOdd()%20give%20%23VALUE%20error%20with%20dynamic%20array%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372539%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659949%22%20target%3D%22_blank%22%3E%40jbwarren%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20the%20support%20pages%20I%20suppose%20that%20ISODD()%2FISEVEN()%20are%20programmed%20differently%20then%20the%20other%20IS%20functions.%3C%2FP%3E%3CP%3EISEVEN()%20expects%20a%20number%20or%20a%20range%20of%20numbers.%20That%20is%20why%20%2BA1%23%20or%20SEQUENCE()%20inside%20ISEVEN()%20work.%20However%20A1%23%20is%20not%20recognized%20as%20a%20(spilled)%20range.%20Not%20really%20a%20bug%20because%20ISEVEN()%20was%20there%20before%20spilled%20ranges%20but%20definitely%20an%20inconsistency%20that%20should%20be%20fixed%20by%20Microsoft.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1374462%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20BUG%3A%20IsEven()%20and%20IsOdd()%20give%20%23VALUE%20error%20with%20dynamic%20array%20parameter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1374462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPotato%20potato%20-%20agreed.%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%20I'm%20not%20aware%20of%20any%20other%20function%20where%20F(expr)%20works%20but%20F(ref%2Frange)%2C%20cell%2Frange%3Dexpr%20doesn't%2C%20with%20the%20possible%20exception%20of%20functions%20explicitly%20working%20on%20type%2Fcell%20metadata%20-%20e.g.%2C%20ROW()%2C%20TYPE()%2C%20...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

 

6 Replies
Highlighted

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

Highlighted

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

Highlighted

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

 

Highlighted

@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

Highlighted

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

 

Highlighted

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