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]

Actually, if we are sure the integer part will always be equal, I believe the above formula can be simplified to this...

=-TRUNC(LOG(ABS(AK351-BU351)))

Rick, I'm not sure if you are suggesting that either formula returns the correct answers, but yours returns
7 correctly for the sample given, but ..

2 (not 1) for the values 1.24 and 1.23
#NUM! (not 1) for 2.3 and 2.3
4 (not 3) for 23569.00050023 and 23569.00040023

 

[michaelsmith559]

Rick, thanks for the formula. Works great. Also, thanks for all the other replies.

 

[MrIfOnly]

I have to say in response to MrIfOnly that I thought I'd captured the decimal point by including the -1 in the Find statements so not really sure why it failed - I guess it has something to do with exactly what example you've tested it against. That makes me concerned that it is not a robust solution - any chance you could show the example that led to the need to modify?

All the best.

Try this: AK351 = 2.1234567891235
BU351 = 2.12345678912345

For some reason it adds one to the count in your original formula and both of Rick's formulas. (It should return 12 but returns 13).

Regards,

CJ

 

[Peter_SSs]

Rick, thanks for the formula. Works great.

Are you sure?
Do you get different results to MrIfOnly and me for the examples we posted in post #13 and #11?

 

[MrIfOnly]

Rick, I'm not sure if you are suggesting that either formula returns the correct answers, but yours returns
7 correctly for the sample given, but ..

2 (not 1) for the values 1.24 and 1.23
#NUM! (not 1) for 2.3 and 2.3
4 (not 3) for 23569.00050023 and 23569.00040023

This is definitely strange, because if you change 1.24 to 1.245 all formulas work as advertised!

 

[pjmorris]

Ahh, I think I see a problem with the approach we've adopted of taking logs.

The difference in your example is numerically small so for example 2.50 and 2.45 will calculate incorrectly as it returns 1 when there are no matching decimal digits. It does look as if the character by character solution is required.

I thought this would be simple!!

 

[Rick Rothstein]

Rick, I'm not sure if you are suggesting that either formula returns the correct answers, but yours returns
7 correctly for the sample given, but ..

2 (not 1) for the values 1.24 and 1.23
#NUM! (not 1) for 2.3 and 2.3
4 (not 3) for 23569.00050023 and 23569.00040023

Hmm, looks like a bad assumption with even worse testing on my part.

Okay, then, how about this. Assuming no more than 15 significant digits maximum, create a defined name for X (yes, just that single letter) with this...

={1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}

and then use this array-entered** formula...

=IF(A9=B9,0,MAX(IF(INT(A9*10^X)=INT(B9*10^X),X)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself


EDIT NOTE: I know we could hard-code the array constant into the formula, but I though that made it kind of ugly looking.

 

[MrIfOnly]

Rick,


Your latest passed all of my tests.

I am, however, going back to suggesting using VBA and converted my code into an UDF simply because it takes just two arguments: range 1 and range 2.

Function findDecPlc(first As Range, second As Range)
Dim i As Long
Dim j
Dim firstNum As Double
Dim secondNum As Double
firstNum = first.Value
secondNum = second.Value
j = 10
    
For i = 1 To Len(first.Value)
    If Int(firstNum * j) <> Int(secondNum * j) Then
        findDecPlc = i - 1
        Exit Function
    End If
    j = j * 10
Next i
findDecPlc = "MATCH!"
End Function

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

Regards,

CJ

 

[pjmorris]

Thats ingenious - i think its going to be years before I could have dreamt that solution!!

 

[Peter_SSs]

=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?

I know we could hard-code the array constant into the formula, but I though that made it kind of ugly looking.

Whilst that is true, I suspect the majority of users would elect that over having to define a name, unless perhaps that name would remove something very long from the worksheet formula. Anyway, it certainly is a valid option.
Of course I have an even longer construct to produce an array in my formula 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.