Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Round to 3 digits (Excel 2002)

    Hi,
    I've gone blank...I know it should be simple, but I can't think now!

    I want to format numbers as R000
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Round to 3 digits (Excel 2002)

    Use (for example) a custom format <code>0,</code> to display thousands, and <code>0,,</code> to display millions.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Round to 3 digits (Excel 2002)

    Lets be difficult:
    Is it possible to display in ten thousands...the user wants 3 digits! I can use =ROUND(A1,-5) to round to 123000.00 but it still shows as 12340 when I use 0, and 12 when I use 0,,
    I want to see 123.

    Any ideas?
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Round to 3 digits (Excel 2002)

    Thank Hans,
    This does the job well!
    Cheers
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Round to 3 digits (Excel 2002)

    I don't think you can do that with a number format. Does this formula do what you want? (Use General format for the result)

    =ROUND(A1/100,0)

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

    Re: Round to 3 digits (Excel 2002)

    I've devised this little macro to round the selection to the specified number of digits:

    <pre>Sub RoundToDigits()
    Dim rCell As Range
    Dim dDigits As Double
    Dim iRoundDigits As Integer
    Dim sFormatstring As String
    Dim iCount As Integer
    Dim vAnswer As Variant
    Dim rRangeToRound As Range
    On Error Resume Next
    Set rRangeToRound = Selection
    If rRangeToRound Is Nothing Then Exit Sub
    vAnswer = InputBox("How many digits?", "Rounding function")
    If TypeName(vAnswer) = "Boolean" Then Exit Sub
    If vAnswer = "" Then Exit Sub
    iRoundDigits = Application.Max(1, vAnswer)
    On Error GoTo 0
    For Each rCell In rRangeToRound.Cells
    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 - 1
    dDigits = Application.Min(Len(Abs(rCell.Value)), dDigits)
    End If
    If dDigits >= 1 Then
    sFormatstring = sFormatstring & "." & String(dDigits, "0")
    ElseIf dDigits < 0 Then
    sFormatstring = sFormatstring & "." & String(iRoundDigits - 1, "0") & "E+00"
    End If
    rCell.NumberFormat = sFormatstring
    End If
    Next

    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Round to 3 digits (Excel 2002)

    The result gives a scientific number. ie 58387036.19 becomes 584E+07?
    Must I format after the macro runs?
    cheers
    Regards,
    Rudi

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

    Re: Round to 3 digits (Excel 2002)

    The code is intended to format to a number of significant digits, but to keep showing the actual value.

    So 1234567 will not be shown as 123 but rather as 1.23E7

    I know this is not what you needed, but I thought the code might come in handy.

    I use this code frequently with measurements that come out of an automated system, showing lots of digits, where-as only 3 to 4 are significant. Since the numbers range from 0.001 to 10000000, my code is very useful.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Round to 3 digits (Excel 2002)

    Thanx Pieterse. The code is handy and will always be!

    I can provide it to the user as another option which I'm sure will be appreciated!
    Cheers
    Regards,
    Rudi

Posting Permissions

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