Calculating an average in VBA and ignore blank cells in range

[totalnatal]

Hello,

I have 10 cells in row A containing weights
I have 10 cells in row B containing values

I'm trying to do a weighted average. however', sometimes i may not have 10 values but maybe 8 or 2.

What code would i need to so that the average ignores the blank cells.

Thank you

 

[kpark91]

Try

Sub WeightedAvg()
'kpark
    Dim i As Integer, weight As Long, val As Long, count As Integer, sum As Long
    For i = 1 to 10 'Assuming your values/weights are side by side and are in col A and col B 1:10
        weight = Range("A" & i).Value
        val = Range("B" & i).Value
        If weight <> 0 And val <> 0 Then
             count = count + 1
             sum = weight * val + sum
        End If
    Next i
 
    MsgBox "Your average is " & sum / count
End Sub

 

[JackDanIce]

I'm assuming that equvalient rows are blank, i.e. if A4 is blank, so is B4

On that basis, try:

Sub Lege()
 
Dim i As Long, j As Long, myAve As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
j = 0

For i = 1 To 10
    If Len(Range("A" & i)) > 0 Then
        myAve = myAve + (Range("A" & i) * Range("B" & i))
        j = j + 1
    End If
Next i
 
If j > 0 Then
    myAve = myAve / j
    MsgBox "Average of the values is: " & myAve
Else
    MsgBox "No values to Average"
End If
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
End Sub