Strange Formating problem with text and formula in same cell

C

caliskier

Hello, I am trying to do something for work where I need text and a
formula in the same cell, and I have a formula that works, see below:

"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"

But the problem is if the value the formula is pulling in is something
like $34.10 per hour, (where the second decimal digit is a zero) the
result shows:

at the rate of $34.1 per Hour

I don’t want it to show $34.1 as the value, I need it to show $34.10, I
need that zero on the end.

I have spent an hour trying to make it work to no avail, does anyone
have any suggestions? Is there a FORMAT function I can stick on
there?

Thanks a bunch!!!
 
R

Ron Coderre

This works:

"at the rate of "&DOLLAR('[RATE.xls]Rate1'!$L$954)&" per Hour"

Regards,
Ron
 
R

Ron Rosenfeld

"at the rate of $"&('[RATE.xls]Rate1'!$L$954)&" per Hour"
Click to expand...

You could use the TEXT worksheet function.

Something like:

"at the rate of "&TEXT([RATE.xls]Rate1!$L$954),"$#,##0.00")&" per Hour"

or, if you wanted the result to be a number that you could use in other cells,
you could use the formula: ='[RATE.xls]Rate1'!$L$954

and the custom format of:

"at the rate of "$#,##0.00" per hour"


Format/Cells/Number/Custom Type: "at the rate of "$#,##0.00" per hour"





--ron
 
Top