Showing fixed # of decimal places in Concatated reference

[cityboy_ca]

Excel 2007

I'm updating a price sheet to show prices with a gas surcharge amount and the actual amount of the surcharge. I'm using the =concatenate command to put both of these prices in one cell. So my formula looks like:

=CONCATENATE(ROUND(Sheet2!B3*(1+$B$3),2)," / ",ROUND(Sheet2!B3*$B$3,2))

where Sheet2!B3 refers to the base price and B3 is a cell on the current sheet with the surcharge percent. My result usually looks like 17.41 / 0.51, which is great. The problem is with 0's. If the result is supposed to be 20.50 / 0.60 it instead shows as 20.5 / 0.60.

I want the extra 0 after the 20.5 and would love to lose the leading 0 in 0.61. I can't do left or right trims since I need it to work for various prices.

Anyone have any thoughts? My skill level is fairly low, so please respond accordingly.

Thanks.

 

[MrKowz]

Try:

=CONCATENATE(TEXT(ROUND(Sheet2!B3*(1+$B$3),2),"0.00")," / ",TEXT(ROUND(Sheet2!B3*$B$3,2),"0.00"))

 

[cityboy_ca]

Thank you so much - worked perfectly. Appreciate it.

 

[dyson]

Fantastic...

I have been looking for a way to ensure that decimals are shown in excel formula bar.

I am now using part of the above formula to calculate from an exported document that shows 999 and 52.1, to make it show 999.00 and 52.10.
Otherwise when I import it back into my EPOS system it wasn't recognising anything that didn't have 2 decimal places.

=TEXT(ROUND(A1,2),"0.00")

However, if anyone can tell me a better or quicker way......


Thanks Paul