CODEWITHSUNDEEP
javaexcelfloating-pointconditional-formatting
Ben I.
Ben I.

Reputation: 1082

Conditional formatting as a Java float

I would like to cause Excel to format a number as Java formats a float, or at least similarly. In particular, I would like to have it display an arbitrary number of digits to the RIGHT of the decimal point, but always have at least a .0 in the case of an integer.

To restate slightly differently: I want Excel to do what it normally does, except if it wants to display an int, in which case it should append .0 to it.

Some examples:

12.0
12.5
 0.0
 0.5368

Never:

12.500000
13
 0

Can Excel even do this?

Upvotes: 1

Views: 239

Answers (2)

Ben I.
Ben I.

Reputation: 1082

Here is how to do it:

Format the cell as General, and then use a Conditional formatting of "=MOD(A1,1)=0" to force a Decimal number with 1 place.

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Assuming your values are all in ColumnA, please try:

  1. A Custom Format of: [<1]0.0000;0.0, coupled with
  2. A Conditional Format formula rule of =A1=0 with Number format and Decimal places: 1 and Applies to =$A:$A

or adjust the range to suit.

If the "arbitrary" is not a selected number of decimal places (assumed four above for numbers less than 1) but say whatever happens to be the result of a formula, then instead:

  1. A Custom Format of Number, Decimal places: 1, coupled with
  2. A Conditional Format formula rule of =AND(A1>0,A1<1) with Format General and Applies to =$A:$A

or adjust the range to suit.

Re clarification of requirement:

as immediately above but change CF formula rule to:

=A1<>INT(A1)

Upvotes: 2

Related Questions