Modulus 11 weigting factor
the following formula calculates the check digit of a number in A1.
=11-MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,6)+2),11)
Can someone please tell me what the weighting factor is within this formula as I will need to set my own weighting factor.
Thank you in advance
Assum a cheque has follow 11 digits:
5,1,2,6,6,7,8,2,5,1,1
each number has its own weighting factor:
2,3,4,5,6,7,2,3,4,5,6
Test by choosing each part of formula, then hit F9:
=11-MOD(SUMPRODUCT({5;1;2;6;6;7;8;2;5;1;1},{2;3;4;5;6;7;2;3;4;5;6}),11)
=11-MOD(189,11)
=9
Quang PT
Hi Bebo21999.
Thank you for your response but I did not quite follow it due to my lack of understanding of modulus numbering.
From the attached screenshot can you determine the weighting factor of the formula. I need to know how to to change the weighting factor.
Attachment 737770
Hi Bebo21999.
Thank you for your response but I did not quite follow it due to my lack of understanding of modulus numbering.
From the attached screenshot can you determine the weighting factor of the formula. I need to know how to to change the weighting factor.
Is it a Luhn algorithm aplication?
You can search on this website. here is some, with my solution:
https://www.excelforum.com/excel-for...tach-file.html
https://www.excelforum.com/excel-for...a-formula.html
Hi Bebo021999, No this is not a Luhn algorithm.
Going back to your first response to me, where do you define with the formula that the weighting is 2 to 7. This is really what I need to work out please.
Thanks again for your help.
Regards
Bingo
That is:
MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,6)+2
Yes but looking at the formula, it looks like the weighting factor is -1 to 6 not 2 to 7.
Please explain further if you can bebo021999
Thanks again
In formula bar, try to highlight the MOD(...) then hit F9, you can see what is result.
Hi Bebo21999, yes this shows how the remainder is calculated, but not the weighting factor.
The weighting factor is the series the number is multiplied by, digit to digit.
If it is 2 to 7, the first digit is multiplied by 2, the second digit is multiplied by 3 and so on, then all the digits in the multiplication result are added up and the remainder after being divisible by 11 is subtracted from eleven, and that is the check digit.
Thus my question still stands, how or where in this formula is the weighting factor defined?
Thank you
That sumproduct did:
SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,6)+2)
=SUMPRODUCT({5;1;2;6;6;7;8;2;5;1;1},{2;3;4;5;6;7;2;3;4;5;6}) = 5*2+1*3+2*4+...+1*6
So Bebo02199 if I wanted to change the weighting factor from 2 to 7 to 4 to 9, where do I make that change in the formula please?
Your help is much appreciated
Just + 2 more to be 4:
MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,6)+4
Thanks Bebo, much appreciated
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi Bebo021999
Thank you for your help in the past.
Can I please ask if we had a weighting which was different for each digit, say as per the following
Digit Weight
0 6
1 3
1 7
1 9
5 10
6 5
9 8
4 4
3 2
3 1
assuming the digit in cell A1 is 0111569433 and the weight is 6, 3, 7, 9, 10, 5, 8, 4, 2, 1
How do we enter this into the algorithm as follows-: =11-MOD(SUMPRODUCT(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
MOD(ROW(INDIRECT("1:"&LEN(A1)))-1,6)+2),11) from your post from above.
Thank you in advance
Start a new thread for this, please. Thank you.
When you do, link back to this thread, use a descriptive thread title and supply a sample workbook.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks