A few Questions on Scoring Sheet

[mrdevon]

Hi there I'm making a darts scoring system for me and my mates and need some help with a few formulas.

Here is what I'd like....

1) when F11 (501) reaches 0 I'd like it to add a 1 to cell G4. I currently have it set as =COUNTIF(F11,0) which works fine for a single game but would like it to count on if possible so when more games are played G4 would read 1, 2, 3 and so on

2) In H7 it displays the average for 3 darts over a leg what I would like to do is keep a personal best so for instance lets say my average in H7 was 77.8, cell A22 also contained the average but if in the the next game I bettered that it would update but if not stay on 77.8 if you understand what I mean

 

[cb12]

Hi there, this is only a crude example of how you could work something like this. I would guess that you might also want to define some variables to move the sets counter forward 1.

The first step it would fail on is if you get the average score with a calculation involving the figures in F7 and K7 and don't update that before you input the checkout score in columns B:D or M:O. If you do input the score first it will calculate the wrong average and take it upto H7 or I7. Anyway, at the very least it will hopefulyl give you something to play around with.

In the relevant sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
 
        Select Case Target.Column
        Case 2 To 4
        Call counter1
        Case 13 To 15
        Call counter2
        End Select

 End Sub

In a module:

Sub counter1()
Dim Legs1 As Integer
Dim BestAvg1, CAvg1 As Double

    Legs1 = Range("G4").Value
    BestAvg1 = Range("H7").Value
    CAvg1 = Range("A22").Value
    
    If Range("F11").Value = 0 Then
        Legs1 = Legs1 + 1
        Range("G4").Value = Legs1
            
        If CAvg1 > BestAvg1 Then
            Range("H7").Value = CAvg1
        End If
    End If

End Sub

Sub counter2()
Dim Legs2 As Integer
Dim BestAvg2, CAvg2 As Single
    Legs2 = Range("J4").Value
    BestAvg1 = Range("I7").Value
    CAvg1 = Range("L22").Value
    
    If Range("I11").Value = 0 Then
        Legs2 = Legs2 + 1
        Range("J4").Value = Legs2
    
        If CAvg2 > BestAvg2 Then
            Range("I7").Value = CAvg2
        End If
    End If
    
End Sub

HTH