Great thanks. The solution you need is actually a formula, rather than VBA, the formula for this would be:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2,ROW(F2:F10)-ROW(F2),0)),--(F2:F10="Yes"))/SUBTOTAL(3,F2:F10)

(Where I picked 10 as TheAnswer row)

Putting this in your code:
ActiveCell.Formula = "=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2,ROW(F2:F" & TheAnswer & ")-ROW(F2),0)),--(F2:F" & TheAnswer & "=""Yes""))/SUBTOTAL(3,F2:F" & TheAnswer & ")"
(Not tested - no workbook)

HTH