Thread: Conditional Format Significant Digits (XL5)

1. Conditional Format Significant Digits (XL5)

Is there a formula for conditionally changing the significant digits in a cell's displayed number. something like: if(value <1, decimal places=3, if(value >1 and <10, decimal places=2, if value >10 and <20, decimal places=1...)?

2. Re: Conditional Format Significant Digits (XL5)

You can nest the IF function to get the results that you want:
<pre>=IF(A1<1, ROUND(A1,3),IF(A1<10,ROUND(A1,2),ROUND(A1,1)))</pre>

You never mentioned negative numbers, you may want to add absolute value:
<pre>=IF(ABS(A1)<1, ROUND(A1,3),IF(ABS(A1)<10,ROUND(A1,2),ROUND(A1,1)) )</pre>

Hope this helps! --Sam

3. Re: Conditional Format Significant Digits (XL5)

This little sub rounds all cells you have selected to the number of digits you enter:

Sub FormatThem()
Dim rCell As Range
Dim dDigits As Double
Dim iRoundDigits As Integer
Dim sFormatstring As String
Dim iCount As Integer

iRoundDigits = InputBox("How many digits?")
For Each rCell In selection
If IsNumeric(rCell.Value) And rCell.Value <> "" Then
sFormatstring = "0"
If rCell.Value = 0 Then
dDigits = 3
Else
dDigits = Log(Abs(rCell.Value)) / Log(10)
dDigits = -Int(dDigits) + iRoundDigits
dDigits = Application.Min(Len(Abs(rCell.Value)) - 1, dDigits)
End If
If dDigits >= 1 Then
For iCount = 1 To dDigits - 1
If iCount = 1 Then sFormatstring = sFormatstring & "."
sFormatstring = sFormatstring & "0"
Next
End If
rCell.NumberFormat = sFormatstring
End If
Next

End Sub

4. Re: Conditional Format Significant Digits (XL5)

Wow. that was quick. The round function was what I was looking for. I've never used that one before. Thanks for the help.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•