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

Please Login or Register  to view this content.

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")

Originally Posted by Haris Zahid

I have used "Text Function" for thousand separator. But it rounds off the figures automatically. here is the formula I am using
=CONCATENATE("Price of one piece is PKR ", TEXT($F$10, "0,0"))
it displays the results as "price of one piece is PKT 72,999"
but I need to include all the decimal numbers with rounding. as 72,998.786.

The format string for the TEXT function is the same as the one you would use for the Custom cell format.

If you wanted the number with comma thousands separator and 2 decimal places, you would use:

="Price is PKR " & TEXT(F10,"#,##0.00")

However, I am confused when you say that you "need to include all the decimal numbers with rounding".

How would you like 72,998.786 to appear: 72,998.79 or 72,998.786?

If the latter, there is no format that truly says "all decimal places". The following might work for you:

="Price is PKR " & TEXT(F10,"#,##0.00#############")

That displays a minimum of 2 decimal places and up to 15 decimal places. Note that Excel formats only the first 15 significant digits (rounded).

Haris Zahid you are hijacking!.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.