Tough Problem 3 (Formula)

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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.......
 

Some videos you may like

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
Joined
Apr 25, 2006
Messages
19,778
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Apr 3, 2020
Messages
3
Office Version
2019
Platform
Windows, MacOS
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
Joined
May 28, 2005
Messages
45,223
Office Version
365
Platform
Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,546
Messages
5,487,489
Members
407,604
Latest member
jortronm

This Week's Hot Topics

Top