Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sig Figs and Other Rules (Excel 2003)

    Hi,

    This significant figures and displaying ending zeros has been irritating me for over 2 weeks ... I have used various posts from this site to help, but I am stuck!
    This company has these rules for displaying a number:
    --If # less than 1, use 3 decimal points--rounded but display ending 0s
    --If 1 <= # < 1000, use 3 significant figures, but display ending 0s
    --If # >= 1000, use rounded whole number
    --Must be a number (not text) so that it can be averaged and graphed

    Using IF and FIXED functions, I get close ...

    In the attached spreadsheet, I use functions. However, I can use VBA and have the users click a button after they enter the data that will generate the number.

    As always, any help/direction is appreciated!

    Thanks,
    Cindy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sig Figs and Other Rules (Excel 2003)

    You could use the Worksheet Change event VBA routine to round the value and set the display format depending on the range the number entered was in.
    Legare Coleman

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

    Re: Sig Figs and Other Rules (Excel 2003)

    If I understand your description and workbook correctly, you could use this procedure:

    Sub FormatCells(rng As Range)
    Dim rngCell As Range
    For Each rngCell In rng.Cells
    If Not rngCell = "" Then
    If IsNumeric(rngCell) Then
    Select Case Abs(rngCell)
    Case Is < 0.995
    rngCell.NumberFormat = "0.000"
    Case Is < 9.95
    rngCell.NumberFormat = "0.00"
    Case Is < 99.5
    rngCell.NumberFormat = "0.0"
    Case Else
    rngCell.NumberFormat = "0"
    End Select
    End If
    End If
    Next rngCell
    End Sub

    To apply it to a range, use a macro like this:

    Sub FormatRange()
    FormatCells Range("C9:C35")
    End Sub

    To format cells as they are entered, you can use the Worksheet_Change event, as suggested by Legare (code goes into the worksheet module)

    Private Sub Worksheet_Change(ByVal Target As Range)
    FormatCells Target
    End Sub

    or if you want to restrict it to a specific range:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C9:C35")) Is Nothing Then
    FormatCells Intersect(Target, Range("C9:C35"))
    End If
    End Sub

    If you also want to format the result of formulas as their arguments change, you'd need to use the Worksheet_Calculate event. But that could cause quite a bit of overhead.

  4. #4
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sig Figs and Other Rules (Excel 2003)

    Thanks, Legare.

    This is the macro I came up with. I had to fudge the ranges, due to rounding, but I think it works.
    I'm not sure where to place it for a Worksheet Change event, though. I'm sorry.

    We have a Quality Control department that takes the displayed numbers in reports and uses a calculator to determine the accuracy of the numbers.
    Since the cell still contains the original number, I will need to use the "Precision Displayed" setting in Options for any further calculation.
    And linking these cells to other locations, means that I will need to run a macro in the other locations, too, if I want the same type of display. The first time the link is done, it picks up the numberformat of the cell. However, if the data/cell changes, the linked cell will still contain the original numberfor. Hmmm... I think I'm getting a headache!

    Anyways, how do I do a Worksheet Change Event?

    Thanks,
    --cat


    Sub DecimalDisplay()
    '
    For r = 9 To 35
    CalcNum = Range("C" & r).Value
    If CalcNum < 0.9995 Then
    Range("D" & r).NumberFormat = "0.000"
    Range("D" & r).Value = CalcNum
    End If
    If CalcNum >= 0.9995 And CalcNum < 9.995 Then
    Range("D" & r).NumberFormat = "0.00"
    Range("D" & r).Value = CalcNum
    End If
    If CalcNum >= 9.995 And CalcNum < 99.95 Then
    Range("D" & r).Value = CalcNum
    Range("D" & r).NumberFormat = "0.0"
    End If
    If CalcNum >= 99.95 Then
    Range("D" & r).NumberFormat = "0"
    Range("D" & r).Value = CalcNum
    End If

    Next r

    End Sub

  5. #5
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sig Figs and Other Rules (Excel 2003)

    Hi Hans,

    I was creating my reply to Legare I guess while you were posting your reply ! You have answered most of my questions ... is Target the name of the worksheet?
    Also, how do you keep the indenting in your replies?

    Thanks,
    --cat

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

    Re: Sig Figs and Other Rules (Excel 2003)

    If you right-click the worksheet tab and select View Code, you'll see the code module for the worksheet. This is where you create event procedures such as Worksheet_Change.

    See the attached version of your workbook.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sig Figs and Other Rules (Excel 2003)

    No, Target is not the name of the worksheet. Target is a parameter of type Range object that is passed to the Worksheet Change event routine. Target is set to the cell or cells that were changed on the worksheet and caused the change event to fire.

    You keep the formatting in code by using [ code] and [ tab] tags around the code. Hans' code would look like this when he posted it (without the spaces after the left brakets):

    [ code]
    Sub FormatCells(rng As Range)
    [ tab]Dim rngCell As Range
    [ tab]For Each rngCell In rng.Cells
    [ tab][ tab]If Not rngCell = "" Then
    [ tab][ tab][ tab]If IsNumeric(rngCell) Then
    [ tab][ tab][ tab][ tab]Select Case Abs(rngCell)
    [ tab][ tab][ tab][ tab][ tab]Case Is < 0.995
    [ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0.000"
    [ tab][ tab][ tab][ tab][ tab]Case Is < 9.95
    [ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0.00"
    [ tab][ tab][ tab][ tab][ tab]Case Is < 99.5
    [ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0.0"
    [ tab][ tab][ tab][ tab][ tab]Case Else
    [ tab][ tab][ tab][ tab][ tab][ tab]rngCell.NumberFormat = "0"
    [ tab][ tab][ tab][ tab]End Select
    [ tab][ tab][ tab]End If
    [ tab][ tab]End If
    [ tab]Next rngCell
    End Sub
    [ /code]

    I use the two Word macros below, adapted from macros that Hans posted, to format code before pasting it into posts.

    <code>
    Sub HLCSpaces2Tabs()
    On Error GoTo Err_Sub
    Selection.Find.Execute FindText:=" ", ReplaceWith:=" ", Replace:=wdReplaceAll
    Exit Sub
    Err_Sub:
    If Err <> 91 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    Sub HLCFormatCode()
    Selection.WholeStory
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Paste
    Selection.WholeStory
    Application.Run MacroName:="HLCSpaces2Tabs"
    Selection.HomeKey Unit:=wdLine
    Selection.TypeText Text:="
    Code:
    "
    	Selection.TypeParagraph
    	Selection.EndKey Unit:=wdStory
    	Selection.TypeParagraph
    	Selection.TypeText Text:="</code>"
    	Selection.TypeParagraph
    	Selection.WholeStory
    	Selection.Copy
    End Sub
    Legare Coleman

Posting Permissions

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