Redundancy check

**Galceran** · 10-08-2008, 11:34 AM

Hi there,
Setting up a worksheet to check a string of 7 numbers has the correct check letter attached. Example
X4032818 X is the check letter. One method is take the 1st number multiply by 1, 2nd multiply by 2, 3rd multiply by 3, 4th x 4, 5th x 5, 6th x 6, 7th x 7. Add the results, in this case equals 123. Divide this result by 25 (the letter Z is not used) the remainder (23) matches the 23rd letter in the alphabet. To cater for when the remainder is 0, the range is 0=A Y=24.
I am using MID to extract each number

Please Login or Register  to view this content.

Instead of six seperate cells in Row C using MID is there a quicker way?

**Andy Pope** · 10-08-2008, 11:39 AM

Assuming you test string is in A1 you could use this to return TRUE or FALSE

=CHAR(65+MOD(SUMPRODUCT((MID(A1,ROW(2:8),1)*ROW(1:7))),25))=LEFT(A1,1)

**sweep** · 10-08-2008, 11:44 AM

=char((mod(sum(mid(c2,{2,3,4,5,6,7,8},1)*{1,2,3,4,5,6,7}),25)+65))

**Galceran** · 10-08-2008, 12:08 PM

It is in C2, changed the A1 ref but it returns #value.
this is what I have in A2: X4032818
B2:

Please Login or Register  to view this content.

C2:

Please Login or Register  to view this content.

D2:

Please Login or Register  to view this content.

et seq
J2:

Please Login or Register  to view this content.

K2:

Please Login or Register  to view this content.

In this instance K2 returns 23, which is X in the lookup table returned in B2
I am trying to consolidate D to J or, is this not feasible

Thanks
Gilbert

**Galceran** · 10-08-2008, 12:13 PM

Thanks Sweep

I am obviously entering your formuals in the wrong cell, where should it go. Does it replace all in D2:J2?

Thanks

**Galceran** · 10-08-2008, 12:26 PM

Right. Put it in D2 and cleared rest of the row.
The object of the exercise is to confirm that X is the correct check letter. It isn't just extracting the X is it ?

Thanks