|
fixed decimals
Is there a formula I can use to fix decimals on values already on a
spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
Select the cells you wish to format........
Right-click FormatCells NumberTab Number set for 4 Decimal places.......... Vaya con Dios, Chuck, CABGx3 "GreenThumb" wrote in message ... Is there a formula I can use to fix decimals on values already on a spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
Try ..
Enter in an empty cell: 10000, and copy it Right-click on the target col Paste special Divide OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "GreenThumb" wrote in message ... Is there a formula I can use to fix decimals on values already on a spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
I have a similar question although its in Quattro pro. I would like to be
able to enter numbers without have to type in the decimal point. Example 1234 automaticly appears 12.34 idealy $12.34. Anyone here know how I could achieve this? "Max" wrote in message ... Try .. Enter in an empty cell: 10000, and copy it Right-click on the target col Paste special Divide OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "GreenThumb" wrote in message ... Is there a formula I can use to fix decimals on values already on a spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
dave
Only on a global basis. ToolsOptionsEdit. Checkmark Fixed Decimal Places and set to two. Individual cells must be done manually or through event code. Gord Dibben Excel MVP On Thu, 14 Apr 2005 12:34:24 -0600, "dave" wrote: I have a similar question although its in Quattro pro. I would like to be able to enter numbers without have to type in the decimal point. Example 1234 automaticly appears 12.34 idealy $12.34. Anyone here know how I could achieve this? "Max" wrote in message ... Try .. Enter in an empty cell: 10000, and copy it Right-click on the target col Paste special Divide OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "GreenThumb" wrote in message ... Is there a formula I can use to fix decimals on values already on a spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
Thanks for replying, would you happen to know the steps to do this manualy,
I have only been able to figure out how to do it to the whole page not just a few select cells. "Gord Dibben" <gorddibbATshawDOTca wrote in message ... dave Only on a global basis. ToolsOptionsEdit. Checkmark Fixed Decimal Places and set to two. Individual cells must be done manually or through event code. Gord Dibben Excel MVP On Thu, 14 Apr 2005 12:34:24 -0600, "dave" wrote: I have a similar question although its in Quattro pro. I would like to be able to enter numbers without have to type in the decimal point. Example 1234 automaticly appears 12.34 idealy $12.34. Anyone here know how I could achieve this? "Max" wrote in message ... Try .. Enter in an empty cell: 10000, and copy it Right-click on the target col Paste special Divide OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "GreenThumb" wrote in message ... Is there a formula I can use to fix decimals on values already on a spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
dave
Manually......... enter 12.34 and format as currency. Worksheet event code........ Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If .Value < "" Then .Value = .Value / 100 .NumberFormat = "$#,##0.00" End If End With End If ws_exit: Application.EnableEvents = True End Sub Select your worksheet tab and "View Code". Copy/paste into that module. Enter 1234 in A1 and see $12.34 returned. If your range is not contiguous you can change the Me.Range like this ("A5,C1,C5,F1,F8,I6,H3,H13,D13,C15,F18,F13") Gord Dibben Excel MVP On Thu, 14 Apr 2005 16:02:11 -0600, "dave" wrote: Thanks for replying, would you happen to know the steps to do this manualy, I have only been able to figure out how to do it to the whole page not just a few select cells. "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . dave Only on a global basis. ToolsOptionsEdit. Checkmark Fixed Decimal Places and set to two. Individual cells must be done manually or through event code. Gord Dibben Excel MVP On Thu, 14 Apr 2005 12:34:24 -0600, "dave" wrote: I have a similar question although its in Quattro pro. I would like to be able to enter numbers without have to type in the decimal point. Example 1234 automaticly appears 12.34 idealy $12.34. Anyone here know how I could achieve this? "Max" wrote in message ... Try .. Enter in an empty cell: 10000, and copy it Right-click on the target col Paste special Divide OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "GreenThumb" wrote in message ... Is there a formula I can use to fix decimals on values already on a spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
dave
A third method, also manual. Select the cells to format using SHIFT + F8 to get into ADD mode then click your way around. InsertNameDefine and name this range. F8 twice to get out of ADD mode. Enter numbers as 1234. When you want to change just those few, enter 100 in an empty cell and copy. From name box select your named range and Paste SpecialDivideOKEsc Gord On Thu, 14 Apr 2005 16:02:11 -0600, "dave" wrote: Thanks for replying, would you happen to know the steps to do this manualy, I have only been able to figure out how to do it to the whole page not just a few select cells. "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . dave Only on a global basis. ToolsOptionsEdit. Checkmark Fixed Decimal Places and set to two. Individual cells must be done manually or through event code. Gord Dibben Excel MVP On Thu, 14 Apr 2005 12:34:24 -0600, "dave" wrote: I have a similar question although its in Quattro pro. I would like to be able to enter numbers without have to type in the decimal point. Example 1234 automaticly appears 12.34 idealy $12.34. Anyone here know how I could achieve this? "Max" wrote in message ... Try .. Enter in an empty cell: 10000, and copy it Right-click on the target col Paste special Divide OK -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "GreenThumb" wrote in message ... Is there a formula I can use to fix decimals on values already on a spreadsheet? I am importing data from a database that assumes 4 decimal places, so a 70000 is really 7.0000. I tried using the fixed formula but that did not give me the desired result. I also went to Tools - Options - Edit - Fixed Decimal Places and set to 4 but that only helps for newly entered data. Please help! Thank You! --GREG-- |
| All times are GMT +1. The time now is 07:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com