How do you find an average of multiple cells only AFTER values have been entered in ALL of those cells?

[sp70]

Hello,
I am working on an electronic score sheet for a pool (8-ball) league. My customer wants to perform a calculation of a NEW player's handicap upon completion of the NEW player's first four games. The problem is that for most new players, they have not yet established a handicap, so users of the spreadsheet would not necessarily know what value to enter for a NEW player's handicap until AFTER the NEW player has played his first four games.

So after the completion of the first four games for that NEW player, then the appropriate cell for the new player's handicap would be populated with the average of those four games.

I am attempting to do this in VBA and I have the following code which I've been switching around in an attempt to get it to work:

        For Each cel In targ.Cells      'Set the Handicap to 9 for Forfeits. Use VLOOKUP formula otherwise.
            If cel = "Forfeit" Then
                cel.EntireRow.Cells(1, 1).Value = 9
                ' Restrict the Handicap value from populating for new players until they've played 4 games.
            ElseIf cel Like "*(NEW)" Then ' Look for the "New" Player value.
               'cel.EntireRow.Cells(1, 1).Value = cel.EntireRow.Cells(1, 8)
                cel.EntireRow.Cells(1, 1).FormulaR1C1 = "=IF(ROUND(IF(COUNT(D[4]:G[4])=4,AVERAGE(D[4]:G[4]),0),0))"
               '"=IF(ROUND(IF(COUNT.Cells(1, 4:1, 7)=4,AVERAGE.Cells(1, 7:1, 7),0),0))"
               
               'cel.EntireRow.Cells(1, 1).FormulaR1C1 = _
               '     "=IF(RC[2]="""","""",IF(COUNTIF(PlayerData,RC[2]),VLOOKUP(RC[2],ValidationLists!C[1]:C[3],3,FALSE),""""))"
            Else  'set the value of the handicap cell to calculate the average of the four games.
                'cel.EntireRow.Cells(1, 1).Value = cel.EntireRow.Cells(1, 8)
                cel.EntireRow.Cells(1, 1).FormulaR1C1 = _
                    "=IF(RC[2]="""","""",IF(COUNTIF(PlayerData,RC[2]),VLOOKUP(RC[2],ValidationLists!C[1]:C[3],3,FALSE),""""))"
            End If
        Next

The 'Forfeit' section above works just fine. As a reference example, a new player would appear in Cell C4 as 'John Smith (NEW)'. Cell A4 would be the handicap. Cell D4:G4 would be the games that would be played, and H4 already does the average calculation as follows:

=IF(COUNT(D4:G4)=4,AVERAGE(D4:G4),"")

No matter what I try, I cannot get Cell A4 to populate correctly for a NEW player's calculated average.

Also, I don't know if my existing lookup value in cell A4 is overwriting or conflicting with the VBA for this particular function:

=IF(C5="","",IF(COUNTIF(PlayerData,C5),VLOOKUP(C5,ValidationLists!B:D,3,FALSE),""))

Please let me know if this does not make sense. If you need, I can also email you a copy of the workbook I have for you to look at.

Thank you,

Shad

 

[Cymae]

You can do as someone suggested to me for a problem, and use:

 With Range(cells you want to check)
                If Application.CountBlank(.Cells) >0 Then 'Counts empty cells 
                Else ' If there are 0 empty cells in the range
                    Logic here
                Endif
End With