Help with converting Excel check sum formula

Question

Hi,We’re automating invoice creation, and need therefore to create payment reference numbers. I’d prefer to create these programmatically within Airtable.As a basis I wanted to use a autonumbering field we already have for creating unique IDs for the orders. Basically the base number will get an added checksum number, so that electronic banking systems can validate that the payer has entered the correct reference number.I found an Excel formula that creates the reference numbers according to our (Finland) banking standards (have tested, it works in Excel). I have been trying to convert it to Airtable, but I can’t get it to work.ORIGINAL:=IF(ISNUMBER(A2);(10*A2+RIGHT(10-RIGHT(7*MID(10000000000+A2;2;1)+1*MID(10000000000+A2;3;1)+3*MID(10000000000+A2;4;1)+(7*MID(10000000000+A2;5;1))+(1*MID(10000000000+A2;6;1))+(3*MID(10000000000+A2;7;1))+(7*MID(10000000000+A2;8;1))+(1*MID(10000000000+A2;9;1))+(3*MID(10000000000+A2;10;1))+(7*MID(10000000000+A2;11;1));1);1));"") Identified change needs:cell A2 => Airtable column (“Numbering”)	separator semi-colon => comma	remove the IF(isnumber) portion (we always have numbers in that column)I have “unit tested” all of the constituent formulas (RIGHT, MID) and they are OK. I have not tested each formula instance, mainly because it gets messy. TESTING VERSION:10*{Numbering}+RIGHT(10-RIGHT(7*MID(10000000000+{Numbering},2,1)+1*MID(10000000000+{Numbering},3,1)+3*MID(10000000000+{Numbering},4,1)+(7*MID(10000000000+{Numbering},5,1))+(1*MID(10000000000+{Numbering},6,1))+(3*MID(10000000000+{Numbering},7,1))+(7*MID(10000000000+{Numbering},8,1))+(1*MID(10000000000+{Numbering},9,1))+(3*MID(10000000000+{Numbering},10,1))+(7*MID(10000000000+{Numbering},11,1)),1),1) Airtable just gives me an error, but doesn’t offer any help to understand the error.NB! MID seems to work with the autonumbering field but RIGHT doesn’t. I hence tested to have the autonumbering field “mirrorred” into a field where I could validate is was seen (and formatted) as a number. This didn’t solve the problem.Would anybody be able to see what to change?Cheers,Björn

0800-grizzly · Answer

This seems to having to do with Airtable’s handling of strings vs numbers. As far as I understood, LEFT, MID and right return strings, not numbers.

I know strings can be forced to numbers by adding +0, and apparently also regular additions on the top result also work the same way. Multiplying or dividing doesn’t give the same result.

But addition or subtraction inside the first RIGHT doesn’t work the same way.

Here are some variations I’ve tested when trying to understand this, with the needed version first:

RIGHT(10-RIGHT(Number, 3),3) = ERROR
RIGHT(RIGHT(Number, 3),3) = STRING
RIGHT(RIGHT(Number, 3),3)-10 = NUMBER (correct)
RIGHT(RIGHT(Number, 3),3)*2 = ERROR
10-RIGHT(RIGHT(Number, 3),3) = NUMBER (correct)
RIGHT(Number, 3)+RIGHT(Number, 3) = NUMBER (correct)

I have tried adding a +0 in many different places, but no-one has worked.

Is there a way to get the “RIGHT(10-RIGHT(Number, 3),3)” to work? I guess that if this can be solved, the any other problem part could be solved the same way.

Cheers,

Björn

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded