cell format round and display 2 decimal places

Question

I have one cell formatted as Number and with 2 decimal places.

The actual number is 69.30217 so in my cell, imagine cell A1, it appears like 69.30. This is OK.

With that cell, I'm making some concatenations so if I do something like this:

"&E5&"

The number appears as 69.30217. But if I do this:

"&ROUND(E5;2)&"

The number appears as 69.3.

What can I do to display that zero? What to show 69.30

Solution 1

Use this

&TEXT(E5;"0.00")&

Solution 2

Another way is to use FIXED function, you can specify the number of decimal places but it defaults to 2 if the places aren't specified, i.e.

=FIXED(E5,2)

or just

=FIXED(E5)

Solution 3

Input: 0 0.1 1000

=FIXED(E5,2)

Output: 0.00 0.10 1,000.00

=TEXT(E5,"0.00")

Output: 0.00 0.10 1000.00

Note: As you can see FIXED add a coma after a thousand, where TEXT does not.

Solution 4

I use format, Number, 2 decimal places & tick ' use 1000 separater ', then go to 'File', 'Options', 'Advanced', scroll down to 'When calculating this workbook' and tick 'set precision as displayed'. You get an error message about losing accuracy, that's good as it means it is rounding to 2 decimal places. So much better than bothering with adding a needless ROUND function.

View more solutions

Comments

[barry houdini]

FIXED function has 3 arguments - the third (optional) argument allows you to specify whether thousand separators are used or not, so to suppress those use =FIXED(E5,2,TRUE)

[mycowan]

Shouldn't that be a comma and not a semicolon? =FIXED(E5**,**2)

[tea_pea]

small note: had to replace ; with , =TEXT(E4,"0.00")

[oyster]

what if "for integer, for example 10, the text is shown as 10; for float, for example 10.1, 2 numbers are used behind decimal point, which is 10.10"?

[Gerhard Powell]

@oyster: Questions should rather be asked in a separate question instead of in a comment...