Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Decimal Places
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Decimal Places
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 (MVP - Excel) "Scott" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Decimal Places
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. "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Scott" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting Decimal Places
Try this formula then...
="$"&TEXT(0.125*E9,"0.00") -- Rick (MVP - Excel) "Scott" wrote in message ... 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. "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Scott" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Numbers with Decimal Places | Excel Programming | |||
formatting decimal places for complex numbers | Excel Programming | |||
conditonally formatting decimal places | Excel Discussion (Misc queries) | |||
formatting decimal places in code | Excel Programming | |||
Currency Formatting to 3 decimal Places | Excel Programming |