Tough Problem 3 (Formula)

[barry houdini]

Hello schielrn,

I think the problem you have is that ISODD and ISEVEN functions don't work with ranges or arrays, they'll only evaluate single cells, even in 2007.

You probably need to use MOD function with a divisor of 2. If the result is zero then it's even, if 1 it's odd.......

 

[pgc01]

I had not tested it yet, but IsEven() and IsOdd() in excel 2007 seem to work ok with arrays, but not directly with a range with more than 1 cell. In that case, it seems we have to first convert it to an array, like:

=SUMPRODUCT(--ISEVEN({1,2,4})) , array OK

=SUMPRODUCT(--ISEVEN(A1:C1)) , Error, range with more than 1 cell

=SUMPRODUCT(--ISEVEN(A1:C1+0)) , OK, range converted into an array


In your case, Schielrn, however, as Barry wrote you can use neither, if the solution is to work in versions previous to xl2007.

 

[barry houdini]

Thanks for the correction, Pedro. I was a little lazy really, I only tested in Excel 2007 with a range and assumed it wouldn't work with an array either.....how wrong I was

 

[bertrand_emerit]

Hello,
10 years after, some small tweaks :

• parsing right-to-left eases the mod() construct
• use of round(mod(n,9.5)) gives what we expect for the algorithm : 0 to 9 when n <= 9 and [0.5->]1 to [8.5->]9 when n >= 10

All this sums up to:
=MOD(SUMPRODUCT(-ROUND(MOD(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)*(MOD(ROW(INDIRECT("1:"&LEN(A1))),2)+1),9.5),0)),10)

 

[Peter_SSs]

10 years after, some small tweaks :

.. or with the advent of the dynamic array functions in Excel 365, a non-volatile adaptation.

=MOD(-SUM(ROUND(MOD(MID(A2,SEQUENCE(,LEN(A2),LEN(A2),-1),1)*(1+MOD(SEQUENCE(,LEN(A2)),2)),9.5),0)),10)