Formatting Decimal Places

[Scott]

I have a formula listed below that is displaying 3 decimal places no matter
what I set the cell number formatting to from the menu. Also you can see I'm
including the "$" symbol in the formula just because that's the only way I
can keep my alignment correct on the sheet.

Is there any excel formula functions that could force my below function to
only display 2 decimals? I also have a problem with the formula results when
the decimal displays as 1 decimal place. Basically I'm looking for a way to
force the formula to return 2 decimal places no matter what. It would also
be nice to force the results to display the thousands comma. The cell format
bdialog options don't seem to work on my results because I'm concatenating
the "$" sign.

Any ideas?



FORMULA:
="$ " & (E9/1000)*125


RESULTS:
$ 532358.375

 

[Rick Rothstein]

If I understand what you are trying to do, why not change your formula back to this...

=(E9/1000)*125

and just format the cells as Currency and set the number of decimal places to 2 within that option. By the way, why not save Excel from having to do a division and just use this simpler formula instead...

=0.125*E9

 

[Scott]

My reason for trying to use code or a formula to format the currency is that
when I use the excel cell fortmatting options to achieve the goal, my user
has to copy/paste these cells into a outlook email message and for some
reason, the "$" gets put on 1 line and the money value flows to a line below
the "$" symbol.

Making the column width wider doesn't seem to help.


If I understand what you are trying to do, why not change your formula back
to this...

=(E9/1000)*125

and just format the cells as Currency and set the number of decimal places
to 2 within that option. By the way, why not save Excel from having to do a
division and just use this simpler formula instead...

=0.125*E9

 

[Rick Rothstein]

Try this formula then...

="$"&TEXT(0.125*E9,"0.00")