how do I take a two digit number, separate the two digits, add them together, show result

I am trying to create a small sheet that applies an algorithm to a nine digit barcode number to calculate the check digit which becomes the 10th.

Attached is the spreadsheet. With the logic of the algorithm included in it.

I've created the method of applying the multiplier to the individual digits of the barcode (In Row 6)

The result of each calculation is in Row 7.

The difficulty is that if the result is a number greater than 9, (in other words two digits) then the number needs to be split - e.g. 14 becomes 1+4 or 18 becomes 1+8, with the sum being the number displayed.

I hope that makes sense - the algorithm is explained in the spreadsheet attached.

I think something like IF result is < 9 , then subtract 10 and add 1 might do it....? But I don't know how to structure that in the cell.

Any help appreciated!

Greg

Hi and welcome to the forum

We would love to help you with your question, but 1st, in accordance with forum rules, please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Also, many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertese, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

Nope, doesnt seem too long to me?

I´m sure there will be a more elegant way to do this but try this on cell C7 and drag across.

=IF(C5*C6>9;LEFT(C5*C6;1)+RIGHT(C5*C6;1);C5*C6)

And for the other formula in L7, see if it works. (For sure a simpler way to get this is available)

=IF(SUM(C7:K7)/10-TRUNC(SUM(C7:K7)/10)=0;0;10-RIGHT(SUM(C7:K7)/10-TRUNC(SUM(C7:K7)/10)))

Try this where C5:K5 is your bar code digits and C6:K6 your algorithm

Formula:

=SUM(INDEX(INT(INDEX(C5:K5*C6:K6,,)/10)+(MOD(INDEX(C5:K5*C6:K6,,),10)),,))

[EDIT]
Rearranged your sample workbook, and made the formula dragable See the attached.

Assuming your alogrithm is constant, you might want to go this way

Formula:

=SUM(INDEX(INT(INDEX(C2:K2*{2,1,2,1,2,1,2,1,2},,)/10)+(MOD(INDEX(C2:K2*{2,1,2,1,2,1,2,1,2},,),10)),,))

See the attached.

simple method
C7: =SUM(--(MID(C5*C6&"0",{1,2},1)))
copy across.
L7: =MOD(10-MOD(SUM(C7:K7),10),10)

or if you just want one cell without the intermediate steps:
L7: =MOD(10-MOD(SUM(--MID((C5:K5*{2,1,2,1,2,1,2,1,2})&"0",{1;2},1)),10),10)
array-entered

there are surely more elegant ways ;-)

Originally Posted by JosephP

L7: =MOD(10-MOD(SUM(C7:K7),10),10)

Cool, for a minute, I thought I read the question wrong. This is a Monica Bellucci formula

Originally Posted by JosephP

or if you just want one cell without the intermediate steps:
L7: =MOD(10-MOD(SUM(--MID((C5:K5*{2,1,2,1,2,1,2,1,2})&"0",{1;2},1)),10),10)
array-entered

I got an error with this one. Entered array, I´m sure it was lost in translation from English to Portuguese syntax.
If it does what it says on the tin, this is a Audrey Hepburn one!

the instructions in the sample imply the final answer is 9 I reckon

I read 9 also, he´s looking for the 10th digit

Joseph, could you attach the array formula in a sheet please? My OCD is not letting this go away

There is Brazilian Portuguese and European Portuguese formula names and it seems none work.

I found that sometimes, the sheet I use for getting English to Portuguese formulas is not properly updated so I would like to know which is the proper one so I can update it with the right answer for this syntax.

yes sure thing

Thanks, my issue was here.

{2,1,2,1,2,1,2,1,2} I had to change the "," to "."
I had it changed before from "," to ";"

Nice one

You could also use this "non-array" version......

=MOD(SUMPRODUCT(-MID(C5:K5*{2,1,2,1,2,1,2,1,2}&0,{1;2},1)),10)

Originally Posted by GregInLondon

Your formula delivers the result of 5 for the check digit, on this sequence:

0 4 12 2 4 5 6 3 2 5

Hello Greg,

I think we are starting from different places, that list, 0,4,12,2,4,5,6,3,2 appears to be the numbers after multiplication by 2,1,2 etc.....

My suggested formula works on the original data labelled "number" in row 5, so from the above list I deduce that the numbers in row 5 are

0,4,6,2,2,5,3,3,1

If those are the contents of C5:K5 then my suggested formula does the whole calculation, multiplies those numbers by 2,1,2,1,2,1,2,1,2, sums all the digits (to get 29) and then calculates the "reverse remainder" to give 1 as the result as you say. Joseph's long formula in post 9 should give the same results

I already did in post 15 ;-)

Here's a sample with some randomly generated numbers (press F9 to re-generate) and with the results for my suggested formula and JP's in columns M and N - you can see that the results are the same.

Joseph's formula needs to be array entered. Put the formula in a cell, press F2 key to select formula then hold down CTRL and SHIFT keys while pressing ENTER. If done correctly the formula is automatically surrounded by curly braces like { and }

Hello Greg,

My suggestion doesn't calculate intermediate results. If you have your 9 barcode digits in D4:L4 then the formula calculates the final check digit directly from those cells, so if you use

=MOD(SUMPRODUCT(-MID(D4:L4*{2,1,2,1,2,1,2,1,2}&0,{1;2},1)),10)

That gives you the check digit (5 in your example) with a single formula.

See attached - I put that formula in N5 and copied to N9 and N13 for the other two examples. The first two match your results - I make the third check digit 4 not 5 (don't forget that 10 = 1+0 =1)

If you want to break it down more then try Joseph's "simple method" in post #9

Good Lord.... I was amazed to be reminded of this thread by tkerr's latest. Over ten years on I can't remember why I needed this - I mean what I was doing to require the calculation. All I can suggest is follow daddylonglegs suggestions in the thread> I presume that he may also have an alert that you posted a follow up - I'll see if I can find a copy of he spreadsheet I refer to below. Very doubtful but we'll see.

Just realised the spreadsheets are downloadable from the thread itself. Just wish I could remember what project or whatever I was working on that caused me to ask in the first place!

What are you working on tkerr?