MOD() Function Returns #NUM! Error Value

[filky]

Hi,

I am trying to create IBAN numbers, so have a formulae with fairly large numbers. I need to do something like this for part of the calculation:

=MOD(C7,97)

C7 could be something like this : 500105170123456799131400

Now, all I get is a #NUM! error. So, I found some info on a very old Microsoft KB explaining how this could be a problem, but that this should work:

=C7-(INT(C7/97)*97)

However, this returns the same error.

Does anyone know if this function is simply useless, or if Excel struggles with a number this size in formulae of this nature?

Thanks

Paul

[Izandol]

Excel may only use 15 significant digits in numbers.

[filky]

That explains the problem then!

Thank you.

Paul

[filky]

Just out of interest... I did eventually find a workaround...

="DE"&98-MOD(MOD(MOD(MOD(MID(+C5&IF(LEN(C4)=6,"0000",IF(LEN(C4)=7,"000",IF(LEN(C4)=8,"00",IF(LEN(C4)=9,"0",
IF(LEN(C4)=10,"")))))&C4&131400,1,6),97)&MID(+C5&IF(LEN(C4)=6,"0000",IF(LEN(C4)=7,"000",IF(LEN(C4)=8,"00",
IF(LEN(C4)=9,"0",IF(LEN(C4)=10,"")))))&C4&131400,7,6),97)&MID(+C5&IF(LEN(C4)=6,"0000",IF(LEN(C4)=7,"000",
IF(LEN(C4)=8,"00",IF(LEN(C4)=9,"0",IF(LEN(C4)=10,"")))))&C4&131400,13,6),97)&MID(+C5&IF(LEN(C4)=6,"0000",
IF(LEN(C4)=7,"000",IF(LEN(C4)=8,"00",IF(LEN(C4)=9,"0",IF(LEN(C4)=10,"")))))&C4&131400,19,6),97)&C5&
IF(LEN(C4)=6,"0000",IF(LEN(C4)=7,"000",IF(LEN(C4)=8,"00",IF(LEN(C4)=9,"0",IF(LEN(C4)=10,"")))))&C4

Unfortunately this doesn't catch every case and I think it may actually be impossible to catch them all with excel because of the way the codes are actually generated (meaning you may need more than just the account number and sort code)... but it was interesting having a play around.

Thanks

Paul

[Izandol]

Thank you for posting - interesting workaround!