Formatting to Two of Fewer Decimal Points

Topic

New Reply

Is there some way to format numbers so they show a maximum of two decimal points. That is, 2 shows as “2”, 2.1 shows as “2.1”, 2.11 shows as “2.11” but 2.111 shows as “2.11”. Just formatting to two decimal points does not work since 2 and 2.1 show as “2.00” and “2.10” respectively.

Ronny

Reply | Quote

Replies

Hi Ronny – Try this on all of your numbers……
=ROUND(A1,2)

Tim

Reply | Quote

0.## as a custom number format will do that.
The decimal point will be displayed, however, in all numbers…
2 as 2.
‘–
Jim Cone
Portland, Oregon USA
Special Sort add-in

Reply | Quote

0.## as a custom number format will do that.

Very cool, just what I needed.

Round will not work. It effects any formulas that reference the number plus =ROUND(2.1,2) displays as 2.10 when formatted to two decimal points; although, if you delete the formatting completely, round works if the number is not referenced in another formula.

Ronny

Reply | Quote

There are features and drawbacks to each solution.

Points to keep in mind … if the numbers were 2.115 and 2.116, using 0.## as a format, the numbers will each “display” as 2.12.

When added together it would “display” like this….
2.12
2.12
—–
2.23

A user may expect to see 2.24 as the total.

Reply | Quote