Show as many Decimals as Needed

[FallingDown]

Hi everyone

I am looking for a way to display a minimum of two decimals, but where needed upto 5.

So let's say with "general" formatting my numbers look like

1005.15
25.252
35694.25698
400
36.147

So the result should be

1,005.15
25.252
35,694.25698
400.00
36.147

Is there any way I could achieve this? Alternatively I am also happy with a VBA code... but I suspect this can be solved with formatting only.

Thanks in advance

FD

[Saarang84]

You can use the ROUND function.

Say the given values are in col A one below the other from A2 onwards, then use,

Please Login or Register  to view this content.

and copy down. This will round the decimals to 2 places.

If these values are generated from a formula, enclose it within the ROUND function as

Please Login or Register  to view this content.

[FallingDown]

Hi Sarang

This, as the function says, rounds it up/down to two decimals
But that's not what I want

As indicated, my numbers are

1005.15
25.252
35694.25698
400
36.147

So the result should be

1,005.15
25.252
35,694.25698
400.00
36.147

So you can see that the numbers still have upto 5 decimals (which should also remain)
But it should add the thousand separator, and show a minimum of 2 decimals

Regards
FD

[estige]

Hi try:
Select cell with 1005,15 (in my example A2)
Home ->
Conditional Formatting ->
New Rule ->
Use a formula to determine which cells to format ->

Format values where this formula is true:
=OR(TRUNC(A2,1)=A2,INT(A2)=A2)

choose number format with 2 decimals
copy format to the other cells

This should format numbers with zero or one decimal, and leave the other numbers as they are.

[Richard Buttrey]

@Saarang84


I believe the OP is looking for a solution that will display as many decimal places as necessary with a minimum of 2 and including the thousands separator.

[estige]

PS I'm not sure how to add separator without at the same time setting number of decimals.

[estige]

PPS You could add one CF-rule for each remaining option (3 4 and 5 decimals) like this:
=TRUNC(A2,3)=A2)
choose number format with 3 decimals and separator

[MrShorty]

Available number format codes (http://office.microsoft.com/en-us/ma...902.aspx?CTT=1) I haven't tested it, but it seems, from the description of placeholders in number formats, that a number format code like #,##0.00### should work just fine if you don't need the decimals to line up, or #,##0.00??? if you do want the decimals to line up.

[FallingDown]

@estige

Perefect! That worked, thanks a lot!!!

FD