# Tough Problem 3 (Formula)

#### barry houdini

##### MrExcel MVP
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.......

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### pgc01

##### MrExcel MVP
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP, Moderator
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)

• bertrand_emerit