Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem with number format with 2 decimals?
I have an Excel 2007 spreadsheet for mileage & fuel gallons on the same
worksheet. Mileage is formatted as a whole number. Fuel gallons I want formatted with 2 decimal places. I do not want to enter the decimal point. I can set the worksheet to "automatic insert a decimal point" to 2 places, but it overides the format for the mileage as a whole number. If I turn off "automatic insert a decimal point", I have to manually insert a decimal point. Ex: Mileage Gallons Driver 3500 Truck 1 125.12 Any suggestions? -- Brenda |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem with number format with 2 decimals?
Brenda
You can use a small macro applicable to only that one column, that would place the decimal point for you whenever you enter a number. You would format that column as General. However, the number of decimal places you want for each and every number would have to be the same. For instance, if you want to enter 15 gallons, you would have to type 1500. If you type 15 you would get ".15". In other words, the macro would simply place the decimal point between the second and third characters from the right. Would that work for you? HTH Otto "Brenda" wrote in message ... I have an Excel 2007 spreadsheet for mileage & fuel gallons on the same worksheet. Mileage is formatted as a whole number. Fuel gallons I want formatted with 2 decimal places. I do not want to enter the decimal point. I can set the worksheet to "automatic insert a decimal point" to 2 places, but it overides the format for the mileage as a whole number. If I turn off "automatic insert a decimal point", I have to manually insert a decimal point. Ex: Mileage Gallons Driver 3500 Truck 1 125.12 Any suggestions? -- Brenda |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem with number format with 2 decimals?
Yes, that's exactly what I wanted! I've never used a macro, but I'm sure I
can figure it out. THANK YOU SO MUCH! -- Brenda "Otto Moehrbach" wrote: Brenda You can use a small macro applicable to only that one column, that would place the decimal point for you whenever you enter a number. You would format that column as General. However, the number of decimal places you want for each and every number would have to be the same. For instance, if you want to enter 15 gallons, you would have to type 1500. If you type 15 you would get ".15". In other words, the macro would simply place the decimal point between the second and third characters from the right. Would that work for you? HTH Otto "Brenda" wrote in message ... I have an Excel 2007 spreadsheet for mileage & fuel gallons on the same worksheet. Mileage is formatted as a whole number. Fuel gallons I want formatted with 2 decimal places. I do not want to enter the decimal point. I can set the worksheet to "automatic insert a decimal point" to 2 places, but it overides the format for the mileage as a whole number. If I turn off "automatic insert a decimal point", I have to manually insert a decimal point. Ex: Mileage Gallons Driver 3500 Truck 1 125.12 Any suggestions? -- Brenda |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem with number format with 2 decimals?
Brenda
Note that this macro applies to only Column B and only to rows greater than 2. Change this as you need. I did this a little different than the way I said before. What this macro does is this: If the entry is not a number, it does nothing. Otherwise, it divides the entry by 100 and formats the cell as a number with 2 decimal places. This gives you what you said you want. Another thing, if you clear a cell that had a number, it will format that cell as "General". You said that you never used a macro before so let me explain a little. Your macro security is probably set to "High" by default. With this security setting the file will open with macros "Disabled" and nothing will work with the macro below. If you have any Excel version other than 2007, click on Tools - Macro - Security. You will see that you have options of High, Medium, and Low. Select Medium. Now when you open any file that has macros, a box will pop up and ask you if you want to open the file with macros enabled or disabled. Choose Enabled. You must place this macro in the sheet module of the sheet in which you want this to happen. To access that module, right-click on the sheet tab and select View Code. Paste this macro in that module. "X" out of the module to return to your sheet. Post back if I've confused you or you need more. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 2 And Target.Row 2 Then Application.EnableEvents = False If IsNumeric(Target.Value) Then If IsEmpty(Target.Value) Then Target.NumberFormat = "General" Else Target.Value = Target.Value / 100 Target.NumberFormat = "0.00" End If End If Application.EnableEvents = True End If End Sub "Brenda" wrote in message ... Yes, that's exactly what I wanted! I've never used a macro, but I'm sure I can figure it out. THANK YOU SO MUCH! -- Brenda "Otto Moehrbach" wrote: Brenda You can use a small macro applicable to only that one column, that would place the decimal point for you whenever you enter a number. You would format that column as General. However, the number of decimal places you want for each and every number would have to be the same. For instance, if you want to enter 15 gallons, you would have to type 1500. If you type 15 you would get ".15". In other words, the macro would simply place the decimal point between the second and third characters from the right. Would that work for you? HTH Otto "Brenda" wrote in message ... I have an Excel 2007 spreadsheet for mileage & fuel gallons on the same worksheet. Mileage is formatted as a whole number. Fuel gallons I want formatted with 2 decimal places. I do not want to enter the decimal point. I can set the worksheet to "automatic insert a decimal point" to 2 places, but it overides the format for the mileage as a whole number. If I turn off "automatic insert a decimal point", I have to manually insert a decimal point. Ex: Mileage Gallons Driver 3500 Truck 1 125.12 Any suggestions? -- Brenda |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem with number format with 2 decimals?
Oh my gosh! Haha!... I will give it a whirl...
-- Brenda "Otto Moehrbach" wrote: Brenda Note that this macro applies to only Column B and only to rows greater than 2. Change this as you need. I did this a little different than the way I said before. What this macro does is this: If the entry is not a number, it does nothing. Otherwise, it divides the entry by 100 and formats the cell as a number with 2 decimal places. This gives you what you said you want. Another thing, if you clear a cell that had a number, it will format that cell as "General". You said that you never used a macro before so let me explain a little. Your macro security is probably set to "High" by default. With this security setting the file will open with macros "Disabled" and nothing will work with the macro below. If you have any Excel version other than 2007, click on Tools - Macro - Security. You will see that you have options of High, Medium, and Low. Select Medium. Now when you open any file that has macros, a box will pop up and ask you if you want to open the file with macros enabled or disabled. Choose Enabled. You must place this macro in the sheet module of the sheet in which you want this to happen. To access that module, right-click on the sheet tab and select View Code. Paste this macro in that module. "X" out of the module to return to your sheet. Post back if I've confused you or you need more. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Target.Column = 2 And Target.Row 2 Then Application.EnableEvents = False If IsNumeric(Target.Value) Then If IsEmpty(Target.Value) Then Target.NumberFormat = "General" Else Target.Value = Target.Value / 100 Target.NumberFormat = "0.00" End If End If Application.EnableEvents = True End If End Sub "Brenda" wrote in message ... Yes, that's exactly what I wanted! I've never used a macro, but I'm sure I can figure it out. THANK YOU SO MUCH! -- Brenda "Otto Moehrbach" wrote: Brenda You can use a small macro applicable to only that one column, that would place the decimal point for you whenever you enter a number. You would format that column as General. However, the number of decimal places you want for each and every number would have to be the same. For instance, if you want to enter 15 gallons, you would have to type 1500. If you type 15 you would get ".15". In other words, the macro would simply place the decimal point between the second and third characters from the right. Would that work for you? HTH Otto "Brenda" wrote in message ... I have an Excel 2007 spreadsheet for mileage & fuel gallons on the same worksheet. Mileage is formatted as a whole number. Fuel gallons I want formatted with 2 decimal places. I do not want to enter the decimal point. I can set the worksheet to "automatic insert a decimal point" to 2 places, but it overides the format for the mileage as a whole number. If I turn off "automatic insert a decimal point", I have to manually insert a decimal point. Ex: Mileage Gallons Driver 3500 Truck 1 125.12 Any suggestions? -- Brenda |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problem with number format with 2 decimals?
Hi,
If you do not want to use a macro, you can type 100 in any cell and then copy this value of 100. After doing so, select the column for gallons and then Edit Paste Special Divide -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Brenda" wrote in message ... I have an Excel 2007 spreadsheet for mileage & fuel gallons on the same worksheet. Mileage is formatted as a whole number. Fuel gallons I want formatted with 2 decimal places. I do not want to enter the decimal point. I can set the worksheet to "automatic insert a decimal point" to 2 places, but it overides the format for the mileage as a whole number. If I turn off "automatic insert a decimal point", I have to manually insert a decimal point. Ex: Mileage Gallons Driver 3500 Truck 1 125.12 Any suggestions? -- Brenda |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number Format Problem | Excel Discussion (Misc queries) | |||
Custom number format problem | Excel Worksheet Functions | |||
How do I change the number format for decimals from , to .? | Setting up and Configuration of Excel | |||
number format problem when using =A1&A2 formula | Excel Discussion (Misc queries) | |||
excel format cells/Number/Category: Number problem | Excel Discussion (Misc queries) |