Help Simplifying Formula

[Tiago MF]

Hi guys

I created a formula to check if a NIF (Fiscal Identification Number) is valid.

The NIF is a 9 digits number, with the last one being a control digit which is calculated using the other eight digits the following way:

1. Multiply the 1st digit by 9, the 2nd by 8, the 3rd by 7, …, and the 8th by 2;
2. Sum up these results and then calculate the mod 11 of this sum;
3. If the remainder is 0 or 1, the control digit will be 0;
4. If it’s another number (x), the control digit is the result of 11 – x

Thus one can make a formula to calculate a control digit based on the first 8 digits of a NIF and then compare it with the 9th digit and check if they are the same. If so, the NIF is valid.

You can see the formula I created on the Upload File, cell D2, which was then copied to the rows below.

Problem is that the formula looks nastily long to me…I was wondering if there’s a clever way to simplify it

I’ve deconstructed my formula on Column G which makes it easier to follow the logic I applied.

Thanks in advance!

EDIT: File uploaded now

 

[Somendra Misra]

Hi Tiago,

No file here.

Regards,

 

[Tiago MF]

It's there now. IE crashed while posting the thread. Thanks Misra

 

[Somendra Misra]

@Tiago MF

My initial suggestion: define name formula for:
1) ROW(OFFSET($C$1,,,8))
2) {9;8;7;6;5;4;3;2}

Regards,

 

[Tiago MF]

Thanks Misra!

Actually one of my main flaws is that I rarely use named formulas, but I reckon they are very helpful in making a formula more readable. I shall use it more in the future.

Thanks again for the suggestion!

Cheers

 

[Haseeb A]

Hello Tiago;

Try this version in D2, then copy down.

=RIGHT(C2)+0=11-TEXT(MOD(SUM(MID(C2,{1,2,3,4,5,6,7,8},1)*{9,8,7,6,5,4,3,2}),11),"[<2]11;0")

 

[Tiago MF]

Amazing Haseeb! That's exactly the kind of simplifying I was looking for! I especially like how you have used the Text function, never seen anything like that before, very interesting.

Thanks a lot!