How do I calculate a check-digit in Excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 26 Feb 2007 23:17 GMT

My math skills are deficient.
I've tried to work with 4 different formulas provided for other check-digit
makers, but my source number is 8 digits, and while it's a simpler formula, I
can't figure it out based on the other formulas:
Step 1 - divide the 8-digit number by 7;
Step 2 - multiply the remainder by 7. (If the remainder is more than one
digit, use only the first digit;
Step 3, Round that answer up to the next whole number. This is the check
digit

Note: If the remainder is zero, it is a whole number. Do not round up.
Zero is the check-digit.

Any help ???
Thanks in advance ...

Reply to this Message

JE McGimpsey - 26 Feb 2007 23:56 GMT

If I understand you correctly:

=ROUNDUP(ROUND(MOD(A1/7,1),1)*7,0)

> My math skills are deficient.
> I've tried to work with 4 different formulas provided for other check-digit
[quoted text clipped - 11 lines]
> Any help ???
> Thanks in advance ...

Reply to this Message

David Biddulph - 27 Feb 2007 00:18 GMT

... or more likely
=ROUNDUP(ROUNDDOWN(MOD(A1/7,1),1)*7,0)

Examples are shown at

Reply to this Message

Dan - 27 Feb 2007 13:20 GMT

Thanks very much David.
Interesting that the exact link to the Customs in-bond entry check-digit
calculator was the source of my question. I' glad you recognized the
question.
Best regards

> .... or more likely
> =ROUNDUP(ROUNDDOWN(MOD(A1/7,1),1)*7,0)
>
> Examples are shown at

Reply to this Message

David Biddulph - 27 Feb 2007 13:48 GMT

It's always wise to quote the reference if you have a specific question,
Dan. The words you quoted didn't make sense without the extra data on the
page. Among other things, it's a strange definition of "remainder".
"Remainder" would normally be MOD(A1,7), not MOD(A1/7,1). Additional
confusion is caused when it says "if the remainder is more than one digit",
as any non-zero remainder with their definition is bound to be more than one
digit.

Signature

David Biddulph

> Thanks very much David.
> Interesting that the exact link to the Customs in-bond entry check-digit
> calculator was the source of my question. I' glad you recognized the
> question.
> Best regards

>> .... or more likely
>> =ROUNDUP(ROUNDDOWN(MOD(A1/7,1),1)*7,0)
[quoted text clipped - 15 lines]
> whole number. Do not round up.>> Zero is the check-digit.>>>> Any help
> ???>> Thanks in advance ...

Reply to this Message

Dan - 27 Feb 2007 14:03 GMT

Thanks again. I'll take your advice and use the source document to ask the
question next time. I've gone to message/info boards a few times previously,
but never gotten an accurate response so quickly.

> It's always wise to quote the reference if you have a specific question,
> Dan. The words you quoted didn't make sense without the extra data on the
[quoted text clipped - 28 lines]
> > whole number. Do not round up.>> Zero is the check-digit.>>>> Any help
> > ???>> Thanks in advance ...

Reply to this Message