variable number formats

K

kpaatu

How can I format a set of cells to dispaly varying decimal place
depending on a value in a different cell.

e.g.,

if "A5" < 1 then format cells G10:G20 to five decimal places,
if "A5" < 10 then format cells G10:G20 to four decimal places,
if "A5" < 50 then fomat cells G10:G20 to three decimal places,
if "A5" < 100 then format cells G10:G20 to two decimal places,
if "A5" < 500 then format cells G10:G20 to one decimal place.

I have used the function, Round(num,x) but this does not do the tric
most of the time. It does not dispaly the last digit if that digi
happens to be a zero. (e.g., 12.1230 is displayed as 12.123)

Thanks
k
 
F

Frank Kabel

Hi
AFAIK as you have 5 conditions this can only be done with VBA using an
event procedure. You may put the following code in your worksheet
module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("G10:G20")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
Select Case .Value
Case Is < 1
.NumberFormat = "0.00000"
Case Is < 10
.NumberFormat = "0.0000"
Case Is < 50
.NumberFormat = "0.000"
Case Is < 100
.NumberFormat = "0.00"
Case Is < 500
.NumberFormat = "0.0"
End Select
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
J

Jonathan Cooper

=TEXT(G10,IF($A$5<1,"#,##0.00000",IF($A$5<10,"#,##0.0000",IF($A$5<50,"#,##0.000",IF($A$5<100,"#,##0.00","#,##0.0"))))

----- kpaatu > wrote: ----

How can I format a set of cells to dispaly varying decimal place
depending on a value in a different cell

e.g.,

if "A5" < 1 then format cells G10:G20 to five decimal places
if "A5" < 10 then format cells G10:G20 to four decimal places
if "A5" < 50 then fomat cells G10:G20 to three decimal places
if "A5" < 100 then format cells G10:G20 to two decimal places
if "A5" < 500 then format cells G10:G20 to one decimal place

I have used the function, Round(num,x) but this does not do the tric
most of the time. It does not dispaly the last digit if that digi
happens to be a zero. (e.g., 12.1230 is displayed as 12.123

Thank
k
 
Top