Tough Problem 3 (Formula)

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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
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 :(
 
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)
 
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)
 

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top