# Thread: Excel-VBA sub routine to calculate AVG

1. Registered User
Join Date
Mar 2004
Location
St. John's, NFLD
Posts
18

## Unanswered: Excel-VBA sub routine to calculate AVG

Hello,

I had developed a sub routine that sums a range of cells M12:M40 that depended on whether or not a checkbox value was true for that particular cell.

I.E. There are 29 cells between M12 and M40 and there is a Checkbox associated with each cell labeled CheckBox1 to CheckBox29.

What I would like to be able to do is take that sum and then get an average based on those checkboxes that are checked.

Here is my code:

Private Sub SumCheckBoxes_Click()

Dim obj As OLEObject
Dim dblValue As Double

For Each obj In ActiveSheet.OLEObjects
If obj.ProgId = "Forms.CheckBox.1" And obj.Object.Value Then
dblValue = dblValue + Range _
("M" & VBA.Replace(obj.Name, "CheckBox", "") + 11).Value
End If
Next obj

'MsgBox dblValue
Sheets("Step 5").Range("M43") = dblValue

End Sub

What I need is some way to count the number of cells that have their checkbox value set to true then divide that number by the sum from the code above.

Thanks for any help I can get

2. Registered User
Join Date
Mar 2004
Location
Fort Worth, Texas, USA
Posts
68
Excel-erate2004,

You're killing me with your insistence on coding VBA, instead of relying on the awesome power of Excel itself. Your checkboxes have the ability to link to a cell--use this to create a column range of 29 TRUE/FALSE values (perhaps N12:N40). You'll have to type in each cell reference in the properties of each checkbox.

After this is done, type =SUM(M12:M40*N12:N40)/SUM(N12:N40) into cell M43. Then press CTRL+SHFT+ENTER

See example attached

#### Posting Permissions

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