compare two decimal values and return number of decimal places correct

[michaelsmith559]

I am looking for a formula that can compare two decimal values and return how many decimal values are correct. Example:
Cell AK351 = 347.980813837264
Cell BU351 = 347.980813859881

I need to compare how many decimal places in cell bu351 are correct compared to cell ak351. In this example the formula would return 7.

Thanks for the help.

Mike

 

[Peter_SSs]

Please let me know if there are improvements to be made...

I'm not suggesting anything wrong with yours, but I had earlier considered a UDF myself before I decided on a formula.
So here is what I had. I'm still assuming any integer part of the two numbers are equal but I'm also assuming each number has a decimal point.

Function CompareDec(s1 As String, s2 As String) As Long
  Dim i As Long
  
  s1 = Mid(s1 & 1, InStr(1, s1, ".") + 1)
  s2 = Mid(s2 & 2, InStr(1, s2, ".") + 1)
  Do While Mid(s1, i + 1, 1) = Mid(s2, i + 1, 1)
    i = i + 1
  Loop
  CompareDec = i
End Function

 

[michaelsmith559]

Just checked your udf against Rick's earlier answer, and your seems to be the more correct one against the values I have.

 

[michaelsmith559]

Just checked your udf against mrifonly, and yours is the most accurate. Excellent. Thanks.

 

[MrIfOnly]

So Peter_SSs wins?! Dang it!

Seriously though, thanks for the challenge.

@Peter_SSs: I like how your UDF handles if one or both of the numbers are entered as text.

Regards,

CJ

 

[Rick Rothstein]

=IF(A9=B9,0,...

I don't understand that. If the 2 numbers are equal, surely the number of "correct" decimal places is the number of decimal places in the numbers?

Good point! Actually, though, I wasn't thinking of that when I wrote it... I was concentrating on two integers both the same in which there are no decimal places at all... my TRUE condition would return 15 for that (given that I restrict my test to 15 significant digits).

...but I elected to cater for any number of possible digits as that would also allow for the case if the values were quite long and entered as text.

Good thought.