2

I want to be able to see every digit, but without any padded zeros on the right.
So I formatted the cells like this:
#,##0.################################################################

However, now Excel is displaying a decimal point for whole numbers as well:

enter image description here

I tried conditional formatting, but unfortunately Excel can't figure out which numbers should be treated as integers - due to quirks in the floating-point math that produced these numbers (Excel functions).

I tried using conditional formatting to highlight integers in red, to see if Excel could differentiate between integers and fractions:

MOD() formula:
=MOD(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE),1)=0

INT() formula:
=INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE)-INT(INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE))=0

Both conditional formatting formulas had the same effect, about 80% accuracy, still failing to catch several instances in my example - I presume due to floating-point math quirks:

enter image description here

Is there a way to consistently format numbers like this?

Or is there a way to get around the floating-point issues, without damaging the accuracy of the values?
(These numbers are being used for scientific measurement purposes, for a mechanical engineering development project, so accuracy is paramount.)

Here are the numbers in CSV format, if you want to test for yourself.
Just know that these numbers are produced by formula calculations in my spreadsheet, so 1760 might actually be 1760.000000000000000000001 or something.

0.0254,1,0.0833333333333333,0.0277777777777778,0.0000157828282828283,25.4,2.54,0.0254,0.0000254
0.3048,12,1,0.333333333333333,0.000189393939393939,304.8,30.48,0.3048,0.0003048
0.9144,36,3,1,0.000568181818181818,914.4,91.44,0.9144,0.0009144
1609.344,63360,5280,1760,1,1609344,160934.4,1609.344,1.609344
0.001,0.0393700787401575,0.00328083989501312,0.00109361329833771,0.000000621371192237334,1,0.1,0.001,0.000001
0.01,0.393700787401575,0.0328083989501312,0.0109361329833771,0.00000621371192237334,10,1,0.01,0.00001
1,39.3700787401575,3.28083989501312,1.09361329833771,0.000621371192237334,1000,100,1,0.001
1000,39370.0787401575,3280.83989501312,1093.61329833771,0.621371192237334,1000000,100000,1000,1
2

I think this is what you want.

1) Format all cells with

 #,##0.################################################################

2) Use conditional formatting with the following formula:

=ROUND(A1,0)=A1

And use it with the following format:

0

(alternatively, use "Number" with zero decimals)

With your data set: enter image description here

  • 1
    The ROUND() function is what I was missing. It doesn't work with MOD() or INT() ... so weird ... – Giffyguy Apr 12 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.