text and formula in the same cell - how do I format this cell??

[pujapatel85]

I've figured out how to put a formula and text in the same cell, thanks
to this group. However, I can't figure out how to format the value of
the cell. For example, I want to create one cell with the following:
Cost $XXX.

The following is what I put in the cell:
="Cost $" & B21*8.

This is the result I get:
Cost $35.32365

I want the value to round to 2 decimal points, but I can't figure out
how to do that. The cell B21 is already in currency form, but the new
value does not show up with a dollar sign. So, I guess I have 2
questions:

1. How do I get this dollar sign to show up automatically (being that
the value is based upon another dollar value?)

2. How do I round the value to 2 decimal places?

Any help is greatly appreciated.

Thanks!!!

 

[Guest]

Format > Cells... > Number > Custom
and then enter "Cost "$#,##0.00
with =8*B21 in the cell

You don't need any concatenation within the cell...just formatting

 

[T. Valko]

Try this::

="Cost " & TEXT(B21*8,"$0.00")

Biff

 

[Trevor Shuttleworth]

="Cost $" & Round(B21*8,2)

Regards

Trevor

 

[Guest]

Good afternoon. First, add the round function to your formula:
="Cost $" & Round(B21*8,2). That will get you your 2 decimal places.
Second, it doesn't matter that B21 is formatted as currency. The cell with
your formula is text. I'm afraid you'll have to continue adding the $ within
the quotes. HTH--
Sincerely, Michael Colvin