Reputation: 7243

cell format round and display 2 decimal places

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

Upvotes: 39

Answers (4)

barry houdini

Reputation: 46331

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)

Upvotes: 39

James Paterson

Reputation: 11

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.

Upvotes: 1