compare two decimal values and return number of decimal places correct

Hi Michael,

Try this: =ABS(LEFT(LOG(AK351-BU351),FIND(".",LOG(AK351-BU351))-1))

It works by taking firstly taking the difference between the two numbers and then calculating the Log10 of that number, before simply removing the initial digit which gives the 'scale' of the number (I think 'scale' is the wrong name, but you get the idea).

HTH

It returns #NUM!

Ya know, I really didn't think this would be possible via a formula, but Peter has proven me wrong. His formula worked great for me (although I added "-1" to the end of it to get a more accurate count).

However, before he posted, and whilst I had it in my thick head that a formula approach wouldn't work, I did it in VBA. So, at least you have a couple of options.


Regards,

CJ

michaelsmith559 said:

It returns #NUM!

Click to expand...

Peter's formula returns #NUM! if AK351 is a smaller number than BU351 or if the two are equal.

I was also incorrect by adding "-1" to the end of his formula -- it counts correctly as-is provided AK351 >
BU351.

Regards,

CJ

pjmorris said:

Hi Michael,

Try this: =ABS(LEFT(LOG(AK351-BU351),FIND(".",LOG(AK351-BU351))-1))

It works by taking firstly taking the difference between the two numbers and then calculating the Log10 of that number, before simply removing the initial digit which gives the 'scale' of the number (I think 'scale' is the wrong name, but you get the idea).

Click to expand...

I think you were on the right track using the LOG function of the difference. I believe this will do what the OP wants...

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

Hi Michael,

Your answer surprised me, but then I managed to get my formula to do exactly the same! It appears dependent on whether you substract one number from the other or vice versa!! I've failed to identify the reason. Consequently I've tweaked the formula as shown below. This now seems to work consistently for me.

=IFERROR(ABS(LEFT(LOG(AK351-BU351),FIND(".",LOG(AK351-BU351))-1)), ABS(LEFT(LOG(BU351-AK351),FIND(".",LOG(BU351-AK351))-1)))

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.

Rick Rothstein said:

I think you were on the right track using the LOG function of the difference. I believe this will do what the OP wants...

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

Click to expand...

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)))

Peter's formula also returned #NUM! for me for that sample data. One issue, but I think not the only one, is that LOG() requires a positive argument.

In any case, I think that with so many decimal places, particularly where the difference lies quite a long way along those decimal places, any formula that does arithmetic calculating with those numbers is likely to run into Excel's "Floating-point arithmetic may give inaccurate results" problem.

I have made the assumption that any whole number part of the pair will be the same & offer this rather lengthy formula as a possibility - only lightly tested.




Edit: Forgot to emphasise that this is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Rick,

As ever a much tidier solution than mine. I did think when I was investigating the #NUM issue that it seemed overly complicated - should have followed that train of thought!

And now I've engaged brain I realise that the #NUM issue is due to not being able to calculate the Log of a negative number!!

I suspect the OP will be very happy with your solution; and I always learn something when I come here!

Regards