I have a list of numbers. They all have decimals. I want to highlight all the cells that have one or two decimal places. Is this possible?
0.001
0.9 <--highlight
0.901
0.902
0.903
0.904
0.905
0.906
0.907
0.908
0.909
0.91 <--highlight
0.911
0.912
I have a list of numbers. They all have decimals. I want to highlight all the cells that have one or two decimal places. Is this possible?
0.001
0.9 <--highlight
0.901
0.902
0.903
0.904
0.905
0.906
0.907
0.908
0.909
0.91 <--highlight
0.911
0.912
I'm optimistic that most things are possible.
My first thought begins with the MOD () function. =MOD (number,0.01). This will be 0 for values with two decimal places, and something non-zero for other values.
The main complication is floating point error (more than you want to know about floating point errors: https://www.excelforum.com/groups/ma...nd-errors.html ). Your "text" list in the OP shows numbers to the nearest thousandth, but we cannot be certain on this side of the internet whether these numbers should be considered exact or not (computers generally cannot represent any decimal number exactly). You need to know enough about your problem to know what precision you expect to test for, then you can use a formula like =MOD (number,0.01) < expected precision. With an appropriate value for expected precision, this formula will return TRUE when the number has one or two decimal places (whenever it is evenly divisible by 0.01) and FALSE when it has more decimal places (is not evenly divisible by 0.01).
Of course "highlighting" (whatever that means to you) is a matter of conditional formatting or however you want to highlight the values.
Will something like that work for you.
Originally Posted by shg
=rounddown(d2,3)=rounddown(d2,2)
The following formula results in TRUE for the two cases that the OP wishes to highlight and FALSE otherwise.
Assuming the data starts in A1:
Formula:Please Login or Register to view this content.
I'm not sure if this fully addresses the issues that MrShorty discusses above, but I did test:
=1.2-1.1
which, given Excel's limited precision, evaluates to 0.0999999999999999000 and yet the above formula "correctly" (or at least per the OP's wish) returns TRUE. BMV's formula above returns FALSE for this case.
Geoff
Did I help significantly? If you wish, click on * Add Reputation to say thanks.
If your problem has been resolved please select ?Solved? from the Thread Tools menu
@GeoffW283 LEN(TEXT(11.91, "general"))
LEN(TEXT(mod(a1;1), "general")) can help but IEEE 754 can brake any solution
Or for different regional settingsPlease Login or Register to view this content.
Please Login or Register to view this content.
Maybe this...
=LEN(REPLACE(A1,1,FIND(".",A1),""))<3
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks