Just so you know, Stefi's formula, as well as the one I posted, work just
fine... against the set-up you told us you had in your original posting
(ABCDEFG in A1, HI in B1). Your "little change" is, in fact, quite a large
change indeed. Not only have you put all you digits in the A1 and separated
them with a dash, BUT NOW you tell us the number of digits can vary as well.
These were not "little changes" by any means. Now, assuming you don't change
your setup again from what you have just told us it is, this formula should
do what you want...

=MOD(SUMPRODUCT((LEN(A1)-ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))))*MID(SUBSTITUTE(A1,"-",""),ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))),1)),10)

--
Rick (MVP - Excel)


"Harshad" wrote in message
...
Dear Stefi,

It's not working.
a little change in my equation:
ABCDEFG-HI-Z, all are independent numerals
A to G are in cell A1, then - (des) HI also in cell A1, and Z in cell B1
Some times the number like, 64-19
Means each time it will not nine digit number, Eg 50-00-Z, 100-01-Z,
1116-70-Z, 17095-24-Z, 101316-73-Z, 1019345-04-Z (the last one is maximum
(seven-two-Z)digits)

Hope this time you will get sucess.

Harshad

"Stefi" wrote:

=MOD(SUM(MID(A1,1,1)*9,MID(A1,2,1)*8,MID(A1,3,1)*7 ,MID(A1,4,1)*6,MID(A1,5,1)*5,MID(A1,6,1)*4,MID(A1, 7,1)*3,MID(B1,1,1)*2,MID(B1,2,1)*1),10)

Check your example, it doesn't suit the formula!

Regards,
Stefi

€žHarshad€� ezt Ã*rta:

My equation is ABCDEFG-HI-Z, all alphabets (A,B,C,D,E,F,G,H,I) have
value
between 0-9.

I have to calculate the value of Z, as follows.

Value of Z is = {( I*1) + (H*2 ) + (G*3 ) + (F*4 ) + (E*5 ) + (D*6 ) +
(C*7
) + (B*8 ) + (A*9 )}/ 10

= Q + Z/10, where Q and Z represents integer and remainder
respectively

For eg. 1034526-98-Z.
Z can be calculated as,

= {( 9*1) + (8*0 ) + (7*3 ) + (6*4 ) + (5*5 ) + (4*2 ) + (3*6 ) +
(2*9 ) +
(1*8 )}/ 10

= {9 + 0 + 21 + 24 + 25 + 8 + 18 + 18 + 8}/10

= 131/10

= 13 + 1/10

So, Z = 1.

In excel, if ABCDEFG numerals are in cell A1, HI numerals are in cell
B1 and
Z is in cell C1. I want formula, which gives the value of Z between
0-9.

Thank in advance.

Harshad