Custom format to divide cells by 100

[timorrill]

Is there a way to set up a custom format that will divide numerical
data by 100?

I do not want to edit the fixed decimals option under Tools > Options

Edit tab, as this will change all the cells in the workbook. Nor do

I want to copy a cell with 100 in it and Paste Special > Divide. I
really am looking for a custom formatting technique.

Thanks!

 

[Ron Coderre]

Maybe something like this:

Select the cells to be impacted...

Then...From the Excel main menu:
<format><cells><number tab>
Category: Custom
Type: 0"."00;-0"."00;0"."00

Note:
If you type 2000, it will display as 20.00
BUT....it will still BE 2000

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Gord Dibben]

There is no formatting method that will actually divide the numerics by 100.

You can get it look like divided by as Ron has shown.

You could use event code to operate on a specific range of cells that would
divide by 100 as the numbers are entered.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const myRange As String = "A1:A10"

If Not IsNumeric(Target.Value) Then Exit Sub
If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then

Application.EnableEvents = False
With Target
.Value = .Value / 100
End With
End If
Application.EnableEvents = True

End Sub


Gord Dibben MS Excel MVP