Custom Format to divide by 10

[Ailish]

Hi All

I was wondering if you can create a custom format to divide by 10. As you
know you can create a custom format for 1000 i.e. 0, and 1m i.e. 0,,. I was
looking to do a similar style for this group of cells and I don't really want
to use the Paste Special and Divide or linking to another cell to divide.

Thanks
Ailish

[Gary76]

Can't think of way of formatting this - hopefully some other bright spark
will...

You could use a macro:

' -------------------
Sub DivideBy10()
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = False Then
cel.Value = cel.Value / 10
End If
Next
End Sub
'-------------

although this would actually divide the values by 10.

HTH anyway...

"Ailish" wrote:

> Hi All
>
> I was wondering if you can create a custom format to divide by 10. As you
> know you can create a custom format for 1000 i.e. 0, and 1m i.e. 0,,. I was
> looking to do a similar style for this group of cells and I don't really want
> to use the Paste Special and Divide or linking to another cell to divide.
>
> Thanks
> Ailish

[MrShorty]

I've done some failry extensive looking for variations on the "," format code theme, and haven't found much. Best answer would depend a lot on your overall goals. Maybe some variation on this theme:

Place the "real" values in column 1
Column 2 then contains the formula =A1/10^n where n is the number of places you want the decimal moved.

Calculations that need to reference the real values will reference column 1, and tables/reports that need to reference the displayed values will reference column 2. I know that the apparent redundancy seems irritating, but it's probably the simplest way to get what you want.