Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Average cells with bold font (2000)

    In my little pride and joy of a workbook I have a right click popup menu which enables the user to choose whether to include the store in the average, or whether to exclude the store from the average (see screenshot below). The right click menu either changes the row font to bold or changes the row font back to normal depending on the user choice - bold for included in the average, or normal for excluded from the average.

    Now that I have the right click menu functioning and works properly, how do I average only the cells with bold fonts? Say between ("b4:b19")?

    Edited to include smaller attachment
    Attached Images Attached Images

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average cells with bold font (2000)

    A non-code solution (on the average) would be to set a flag in another cell that indicated the row bold True/false status.

    Then the average formula will be similar = SUMIF(range,criteria,sum_range) / SUM(flag range = true) (Arrayentered)

    I hope it helps

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Average cells with bold font (2000)

    [Edited, sorry about that first post!]

    Here's a generic user-defined function, but due to the powers that sign my paycheck wanting me to work right now, can you test to see if this works as it should?

    Private Function AVGBOLD(rngABRange As Range) As Double
    Application.Volatile
    Dim rngCell As Range
    Dim dblCountBold As Double
    Dim dblSumBold As Double
    AVGBOLD = 0
    dblCountBold = 0
    dblSumBold = 0
    For Each rngCell In rngABRange
    If rngCell.Font.Bold = True Then
    dblSumBold = dblSumBold + rngCell.Value
    dblCountBold = dblCountBold + 1
    End If
    Next rngCell
    AVGBOLD = dblSumBold / dblCountBold
    End Function

    If you place it in the module attached to the WB where you use it, usage is:
    =AVGBOLD(range)

    If you place it in the module attached to Personal.xls, usage is:
    =Personal.xls!AVGBOLD(range)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Average cells with bold font (2000)

    John,

    May I offer an amendment that takes care of non-numeric values.<pre>Function AVGBOLD(rngInput As Range) As Double
    Application.Volatile
    Dim rngCell As Range
    Dim lngCountBold As Long
    Dim dblSumBold As Double
    AVGBOLD = 0
    lngCountBold = 0
    dblSumBold = 0
    For Each rngCell In rngInput
    If rngCell.Font.Bold And IsNumeric(rngCell) Then
    dblSumBold = dblSumBold + rngCell.Value
    lngCountBold = lngCountBold + 1
    End If
    Next rngCell
    AVGBOLD = dblSumBold / lngCountBold
    End Function</pre>

    Also changed the count type to Long.

    Andrew C

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Average cells with bold font (2000)

    Good catch, thanks Andrew.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average cells with bold font (2000)

    Thanks everyone, two good solutions (Kieran, did yours last night, John and Andrew tried yours a few minutes ago, both work wonderfully). This right click stuff is fairly amazing.

Posting Permissions

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