Help getting averages for each person for each month

I really need some help

I have attached the Excel sheet I am working with as you can see its got the all the working days of Jan and so far for Feb, well this will grow over the 12 months all the way to end of the year.

The problem is I need to get the average of each person figures for each month i.e.

Person A, Jan Average, Feb Average.

The average will be the total for the month divided by the days of that month worked (anything blank is seen as a day off).

One of the issues is the data comes from an old AS400 system and through filtering out all the nonsense data we seem to be stuck with a blank line in each month. i.e. data is the work blank and the whole line is a blank which is easy tog et around by just -1 from the total of days calculated.

Thing is I've already written a load of code and its all IF statements within IF statements.

So far it can read Just January for one person and give a total and I am concerned that this is going to turn into a beast for some simple figures.

The biggest issues is that this is updated and sent to managers and directors daily so its needs to be less of a daily chore to update the figures.

Does anyone one have any simpler ideas as to achieve this without what currently appears to be 100s of lines of VBA.

There is one thing, it maybe that more staff could come and work in a year so they would need adding to the sheet of data if needed so be another column header and figures to work with.

I hope you guru's have some good advice.

KR

Kevin

Thank you went with the Jindon code in the end but its great there are so many different ways I didn't even know.

Thank you Alan this did work too but decided to use the VBA in the end as it fitted the job better for automation.

VBA

Sub test()
    Dim a, i As Long, ii As Long, myMonth As String, temp As Date, SL As Object, w
    Set SL = CreateObject("System.Collections.SortedList")
    With Sheets("sheet1")
        a = .Range("a1", .Cells.SpecialCells(11)).Value
    End With
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For ii = 2 To UBound(a, 2)
            If a(4, ii) = "" Then Exit For
            Set .Item(a(4, ii)) = CreateObject("Scripting.Dictionary")
        Next
        For i = 5 To UBound(a, 1)
            If IsDate(a(i, 1)) Then
                temp = DateAdd("d", -Day(a(i, 1)) + 1, a(i, 1))
                myMonth = Format$(a(i, 1), "mmm ""(""yyyy"")")
                If Not SL.Contains(temp) Then SL(temp) = myMonth
                For ii = 2 To UBound(a, 2)
                    If (a(i, ii) <> "") * (.exists(a(4, ii))) Then
                        If Not .Item(a(4, ii)).exists(myMonth) Then
                            ReDim w(1 To 2)
                        Else
                            w = .Item(a(4, ii))(myMonth)
                        End If
                        w(1) = w(1) + a(i, ii): w(2) = w(2) + 1
                        .Item(a(4, ii))(myMonth) = w
                    End If
                Next
            End If
        Next
        ReDim a(1 To .Count + 1, 1 To SL.Count + 1)
        For ii = 0 To SL.Count - 1
            a(1, ii + 2) = SL.GetByIndex(ii)
        Next
        For i = 0 To .Count - 1
            a(i + 2, 1) = .keys()(i)
            For ii = 2 To UBound(a, 2)
                If .items()(i).exists(a(1, ii)) Then
                    a(i + 2, ii) = .items()(i)(a(1, ii))(1) / .items()(i)(a(1, ii))(2)
                End If
            Next
        Next
    End With
    Sheets.Add.Cells(1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Brilliant thank you Jindon worked a treat.