Calculating Data in VBA Excel Where conditions can vary

Question

I am coaching a rifle shooting team and I am designing a program to analyse the data, all the data required is stored in a large denormalized on a sheet in excel. I am hoping to calculate statistics for each shooter such as Average Score, Top Score, Std Dev etc. How ever on my summary sheet I also need a bunch of variables to filter the data with such as Day of the year, time of day, distance shot, the year.

I am attempting to design my own fuctions such as

Public Function AveScore(ShooterID As String, YearShot As Integer, Optional Day As String = "All Year", Optional TimeOfDay As String = "All Day", Optional Distance As String = "All", Optional OutOf As Integer = 40, Optional TopShots As Integer = 0, Optional AdjOutOf As Boolean = True) As Double

This then I will be able to call easily on my sheets and allow me to easily adapt my format. However due to the nature of the filters they will sometimes filter some data and other times I would like all data in those areas. Each filter corresponds to a field in the table.

I found that using loops to calculate the results ended up with many many overwhelming if statements which was unrealistic because I had to account for each eventuality for when certain conditions were needed or not

the following is my attempt as writing it by creating an excel formula through if statements then evaluating this

Public Function AveScore(ShooterID As String, YearShot As Integer, Optional Day As String = "Al Year", Optional TimeOfDay As String = "All Day", Optional Distance As String = "All", Optional OutOf As Integer = 40, Optional TopShots As Integer = 0, Optional AdjOutOf As Boolean = True) As Double
'if TopShots is 0 then no top and show all shots

Dim formula As String: formula = "{=AVERAGE("
Dim top As Boolean: top = False
Dim i As Integer

'check if they want to look at the top shots
If TopShots <> 0 Then
    top = True
    formula = formula + "LARGE("
End If
'add 2 if statements that check year and shooterID
formula = formula + "IF(DenormalizedTable[Year]=" & YearShot & ",IF(DenormalizedTable[ShooterID]=" & Chr(34) & ShooterID & Chr(34) & ","

Dim counter As Integer: counter = 2

'check if its a day or period the add the IF statement
If Day <> "All Year" Then
    Set c = Lists.Range("PeriodList").Find(Day, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
        formula = formula + "IF(DenormalizedTable[Period]=" & Chr(34) & Day & Chr(34) & ","
        counter = counter + 1
    Else
        Set c = Lists.Range("DayList").Find(Day, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            formula = formula + "IF(DenormalizedTable[Day]=" & Chr(34) & Day & Chr(34) & ","
            counter = counter + 1
        End If
    End If
End If

'and the time of day if
If TimeOfDay <> "All Day" Then
    formula = formula + "IF(DenormalizedTable[TimeOfDay]=" & Chr(34) & TimeOfDay & Chr(34) & ","
    counter = counter + 1
End If

'Add the distance IF
If Distance <> "All" Then
    formula = formula + "IF(DenormalizedTable[Distance]=" & Distance & ","
    counter = counter + 1
End If

formula = formula + "DenormalizedTable[%Score]"

'add the brackets for the IFs
For i = 1 To counter
    formula = formula + ")"
Next i

If top Then
    formula = formula + ",{"
    For i = 1 To TopShots
        formula = formula + i
        If i <> TopShots Then
            formula = formula + ","
        End If
    Next i
    formula = formula + "})"
End If

formula = formula + ")*" & OutOf & "}"
MsgBox formula
AveScore = Evaluate(formula)
End Function

How ever this function also seems very impractical and unextendable for the other functions I wish to write

I was hoping someone would be able to suggest a way I might achieve this analysis that is practical

I am only new to coding and do not have the best knowledge of prebuilt functions however I have done lots of research and could not find anything that would help me