# Thread: Average cells with bold font (2000)

1. ## 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

2. ## 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. ## 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)

4. ## 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. ## Re: Average cells with bold font (2000)

Good catch, thanks Andrew.

6. ## 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
•