Problem with ISODD and other function when used in array formulas.
If i use this array formula:
I get a nice array back but, if I use it over a range I doesn't work
Please Login or Register to view this content.
1.pngPlease Login or Register to view this content.
If I transpose the range twice it works, which doesn't make any sense to me
2.pngPlease Login or Register to view this content.
I would imagine it's to do with the fact that ISODD is treating the range as 2-dimensional (i.e. 1 to 1, 1 to 5), unlike the (horizontal) array constant (1x5)
the two transpositions would convert the (1 to 1, 1 to 5) to 1-dimensional array (1 to 5)
in VBA terms
FWIW, you could seemingly do the same with MOD without the need for TRANSPOSE - i.e. =(MOD(B3:F3,2)=0)+0 {CSE}Please Login or Register to view this content.
Yeah i would imagine that is the problem also. But actually it still doesn't make a lot of sense to me that it works with the MOD function and not the ISODD function...
This does work.
Attachment 682921Please Login or Register to view this content.
I would image that all functions would have the same behavior but ISODD for example gives #VALUE! error as soon as it gets the range.
I would like to understand the why as I'm "studying array formulas" because I made a small evaluation engine that understands excel formulas and in my engine that works fine but in excel it doesn't.so users have some problems when they try the same folumas in excel.
Last edited by pfigueiredo; 06-17-2020 at 08:03 AM.
Hasn't that to do with what function can properly coerce arrays?
Maybe XL XOR or Bo_Ry can better explain, try: =IF(ISODD(N(IF(1,B2:F2))),0,1)
Is that what you were looking for?
Although I find festing for ISODD and then marking the even numbers with 1 strange?
Why not use ISEVEN then?
Using ISODD was just a test case I didn't expect the formula to make any sense.
Anyway is there any documentation on functions that don't properly coerce arrays?
Found some insight here:
excelxor.com/2014/11/04/coercing-array-returns-from-cse-resistant-formulas/
Any more info is appreciate.
But may thanks for all the help.
Last edited by pfigueiredo; 06-17-2020 at 08:39 AM. Reason: Some extra information found googleing
yes, just FYI, the author is @XOR LX - as referenced by @RaulSerg above.
I was reading some older posts too where it was also noted that, for whatever reason, the older EVEN and ODD functions do work with Arrays
B4:F4: =(EVEN(B3:F3)=B3:F3)+0
https://www.excelforum.com/excel-gen...-of-cells.html
of course, this doesn't resolve your underlying issue, but adds to the weirdness - and so 'interesting' nonetheless... (for some of us!)
Hi, I think my previous formula was off.
Can you try this and see if that works for you?
=N(ISODD(+$F$6:$J$6))
or if you prefer =--ISODD(+$F$6:$J$6)
The + is crucial in the formula.
Last edited by RaulSerg; 06-17-2020 at 10:38 AM.
It just seems some functions' native behavior is to accept the first value in a range only. This is particularly true of all the date functions as near as I have seen. The rest of the values must be coerced. I use just the "+" =IF(ISODD(+B3:F3),0,1)
I also picked that up from XOR LX.
It also works for coercing text ranges.
FWIW XOR LX has a host of articles RE: Array coercion. Many of the solutions offered by other contributors in the challenges sections reveal a lot of additional techniques.
Have fun!
Dave
As an additional piece of information, even in Excel365 with dynamic arrays e.g. =ISODD(+F13:J13) spills correctly but also only if the unary + is included, too.
This is a Fantastic comunity you guys are awesome!
Thanks for all the insights on this!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks