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

sp70

New Member
Joined
Nov 2, 2008
Messages
1
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:

Code:
        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:

Code:
=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:
Code:
=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

Board Regular
Joined
Oct 19, 2008
Messages
70
You can do as someone suggested to me for a problem, and use:

Code:
 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
 

Forum statistics

Threads
1,081,538
Messages
5,359,397
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top