I’m trying to port over my UPC check digit calculator formula over to airtable but they don’t support “sumproduct”. Here is the formula, can someone help me find a solution in AT?
Excel Formula
=A1 & 10 - MOD( SUMPRODUCT(MID(A1, {1,2,3,4,5,6,7,8,9,10,11}, 1) * {3,1,3,1,3,1,3,1,3,1,3}), 10)
I can’t provide an Airtable equivalent for SUMPRODUCT(), as Airtable doesn’t currently support array arithmetic, but I can provide you with a formula to calculate the check digit.g1]
This algorithm assumes your 11-digit UPC code is stored in a field called, unsurprisingly, {UPC}. This formula should work with both number and single-line text {UPC} fields. I’ll give you two versions of the formula; the first simply returns a single check digit, which should be appended to your 11-digit code.
Note: There are undoubtedly easier and/or more elegant ways to perform this function; I stopped once it started working. If you want something prettier, knock yourself out.
IF(
MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)=0,
0,
10-MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)
)
The second version returns the completed 12-digit UPC:
REPT('0',11-LEN(UPC&''))&UPC&
IF(
MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)=0,
0,
10-MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)
)
You can find a demonstration base here that demonstrates the use of this formula with both number and text fields.
Dude your a gem!
Worked like a charm.
Thank you so much
I can’t provide an Airtable equivalent for SUMPRODUCT(), as Airtable doesn’t currently support array arithmetic, but I can provide you with a formula to calculate the check digit.g1]
This algorithm assumes your 11-digit UPC code is stored in a field called, unsurprisingly, {UPC}. This formula should work with both number and single-line text {UPC} fields. I’ll give you two versions of the formula; the first simply returns a single check digit, which should be appended to your 11-digit code.
Note: There are undoubtedly easier and/or more elegant ways to perform this function; I stopped once it started working. If you want something prettier, knock yourself out.
IF(
MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)=0,
0,
10-MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)
)
The second version returns the completed 12-digit UPC:
REPT('0',11-LEN(UPC&''))&UPC&
IF(
MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)=0,
0,
10-MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)
)
You can find a demonstration base here that demonstrates the use of this formula with both number and text fields.
Thank you so much for this @W_Vann_Hall! This helped immensely with trying to figure out this formula, worked great.
I am not great with excel but have a formula that helps calculate other types of barcodes, by chance would you know how to translate it to Airtable?
This is the formula from excel:
1&@ColumnA&MOD(-SUM(MID(1&@ColumnA&0,{1,3,5,7,9,11,13;2,4,6,8,10,12,14},1)*{3;1}),10)
It grabs a number from the column named ColumnA and proceeds to do the following calculations on the individual numbers in each position up to position 14
I can’t provide an Airtable equivalent for SUMPRODUCT(), as Airtable doesn’t currently support array arithmetic, but I can provide you with a formula to calculate the check digit.g1]
This algorithm assumes your 11-digit UPC code is stored in a field called, unsurprisingly, {UPC}. This formula should work with both number and single-line text {UPC} fields. I’ll give you two versions of the formula; the first simply returns a single check digit, which should be appended to your 11-digit code.
Note: There are undoubtedly easier and/or more elegant ways to perform this function; I stopped once it started working. If you want something prettier, knock yourself out.
IF(
MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)=0,
0,
10-MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)
)
The second version returns the completed 12-digit UPC:
REPT('0',11-LEN(UPC&''))&UPC&
IF(
MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)=0,
0,
10-MOD(
((
VALUE(MID(UPC&'',LEN(UPC&''),1))+
VALUE(MID(UPC&'',LEN(UPC&'')-2,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-4,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-6,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-8,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-10,1))
)*3+
VALUE(MID(UPC&'',LEN(UPC&'')-1,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-3,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-5,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-7,1))+
VALUE(MID(UPC&'',LEN(UPC&'')-9,1))
),
10
)
)
You can find a demonstration base here that demonstrates the use of this formula with both number and text fields.
Wow, I was already having nightmares thinking about how I was going to do this function.
You deserve at least a coffee, where's your "pay me a coffee" website ?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.