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:
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:
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
Best Answer
I think this is what you want.
1) Format all cells with
2) Use conditional formatting with the following formula:
And use it with the following format:
(alternatively, use "Number" with zero decimals)
With your data set: