Excel identifying (counting) decimal places

[kjschmitz]

Hello all, I have been getting a lot of great advice from many of you by reading through previous posts, but have stumbled upon an issue that I cannot seem to find a solution for:

I am currently working in Excel 2003 (although I have access to 2002-2007) and am going to be undertaking a project in which data will be imported as two separate columns from two different sources. What I need to do is:

1) Check that the values in column one are equal to those in column two (easy enough to do and I have done this)

2) Count the number of decimal places used in each to ensure that the number of decimal places are equal between the two values. For example, one of the sources of data sometimes rounds to 7 digits, and another always uses six.

I do not want to change the rounding by formatting the cells, just 'flag' the values in some way so that I am aware of the greater level of precision.

Any help would be much appreciated, and I anticipate your responses. Thank you in advance.

 

[SkipVought]

Hi,

"I do not want to change the rounding by formatting the cells..."

FORMATTING changes NOTHING. In other words, formatting does not change the underlying value, just what you see displayed.

If the two values are equal, what does the count of decimal places matter? If you have two values, one rounded to 6 and the other to 7, then they are not equal.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[kjschmitz]

First off thanks for your response. I was unclear in what I am looking for.

Basically, there are two reasons my data are not equal. First, because the decimal rounding by the input sources is different. Second, because the values generated by the two sources are different in value (not rounding).

I want to be able to see if they are equal (no further changes needed). If they are not equal I want to know if it is because the first has only 6 decimals verses 7 (the 7th digit being extraneous information in this case.)

I just don't want to have to check every non-equal case to find the ones that have an extra digit (as there will be thousands of values eventually).

Thanks again.

 

[SkipVought]

But you could have two values like this...

1.123456
7.2345678

So what is comparing the number of decimal places really telling you?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[mintjulep]

Seems like the simplest thing would be just check the length of the entire number with LEN().

If you really need to check the number of significant digits only, then

=LEN(A1-INT(A1))-2

 

[xlbo]

=len(A1)-find(".",A1)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kjschmitz]

Touche, but here is an example:

Column A Column B
1 1.236699 1.272365
2 1.247763 1.247763
3 1.2356961 1.235696


Case 1: these values are different and should be flagged as such (easy part)

Case 2: these values are the same and should be ignored (also easy)

Case 3: these values are only different because of some operation performed by a different program/machine that I have no access to.

These values are not "equal" in the eyes of excel when I use conditional formatting. But only because there is an extra digit in column A that was "rounded out" for whatever reason from the input source of column B.

Basically if I can find a function (ie macro) that I can use to count digits after the decimal point I can write whatever functions I need from there.

Thanks again!

 

[SkipVought]

I would think that the mathematical DIFFERENCE would tell you more than counting decimal places.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[kjschmitz]

Thanks guys, that works great! I did need the sig digits portion as some values are >10, etc. Thanks again.

 

[AnotherHiggins]

How about this approach:
[tt]
Value1 Value2 Equal? Rounded Equal?
1.236699 1.272365 =A2=B2 =ROUND(A2,6)=B2
1.247763 1.247763 =A3=B3 =ROUND(A3,6)=B3
1.2356961 1.235696 =A4=B4 =ROUND(A4,6)=B4
[/tt]

That will return:
[tt]
Value1 Value2 Equal? Rounded Equal?
1.236699 1.272365 FALSE FALSE
1.247763 1.247763 TRUE TRUE
1.2356961 1.235696 FALSE TRUE
[/tt]

Sort by column D to group all of the truly different values together.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[macropod]

OK, using numbers stored in Excel, how is 0.123456 different from, or less precise than, 0.1234560? And what about 0.1 vs 0.1000000? Bear in mind that, even if the trailing 0s exist in the imported data, Excel will drop them unless the data are imported as text.

I can't see what flagging the data will achieve where the trailing 0s have been dropped, and I don't know how you can infer that the 'missing' digit(s) weren't 0s in either data set.

Cheers

[MS MVP - Word]

 

[ck1999]

The precision can depend on significant figures especially in the scientific arena.
0.123456 is not as precise as 0.1234560 because you have 1 more significant digit.

0.123456 can be 0.1234555 - 0.1234564
0.1234560 can be 0.1234595 - 0.1234604

ck1999

 

[macropod]

Hi ck,

That may be so, but Excel drops the trailing 0s, so you can't see the original value's precision. That's why I qualified my post with *using numbers stored in Excel*!!!

Cheers

[MS MVP - Word]

 

[SkipVought]

Leading and trailing zeros regarding NUMBERS is ONLY A DISPLAY feature.

Formatting changes NOTHING in the underlying value of a number. NOTHING!!!

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[RivetHed]

Formatting changes NOTHING in the underlying value of a number. NOTHING!!!"

Skip, you should just start putting that in your signature.

 

[SkipVought]

"I know NOTHING!" _{Sgt Shultz}

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}