Thread: Number Format from Round Function within Concatenate

I'm using a round function within a concatenate function. I am rounding to 2 decimal places, but if the result has trailing zeroes, the round function will delete them. I'd like for it to always display two decimal places.

For example, if I have numbers 2.999 and 4.354 and I want a cell that displays "3.00-4.35" so I use the formula

=concatenate(round(2.999,2),"-",round(4.354,2))

But this displays "3-4.35" instead.

I can't use the "Format Cells" option, because the cell is no longer in number format. Trying a "custom" format like "#.##-#.##" does not work. I also tried the "Set Precision as Displayed" option, but it did nothing.

Is there any way I can force Excel to display the trailing zeroes when my numbers are calculated by functions?

Thanks!

zeze,

Give this a try:
=TEXT(ROUND(2.999,2),"0.00")&"-"&TEXT(ROUND(4.354,2),"0.00")

You can also use FIXED function to set the number of decimal places - it defaults to 2 so if you don't specify that's what you get.....

=FIXED(2.999)&"-"&FIXED(4.354)

I just recently saw a user using that same FIXED function... and thought why not use it instead of TEXT for these situations... coincidental...

It's a good alternative to know.......

.....of course if you use TEXT then ROUND isn't really required, this should be sufficient

=TEXT(2.999,"0.00")&"-"&TEXT(4.354,"0.00")