problem with number format with 2 decimals?

[Brenda]

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?

 

[Otto Moehrbach]

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]

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!

 

[Otto Moehrbach]

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]

Oh my gosh! Haha!... I will give it a whirl...

 

[Ashish Mathur]

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