Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Aiken, SC
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    May 2002
    Location
    Aiken, SC
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •