Issue with AVERAGEIF

[Opv]

I am trying to insert the following AVERAGEIF formula:

=AVERAGEIF(DAY(Dates),Day(A4),Sys)

I receive a "this formula contains an error" message each time I attempt to enter it. I have double checked my named ranges and they are all the same length. Any idea what might be going on?

[Paul_Hossler]

Post a small workbook with the named ranges and the formula

This will make it easier to see

[Opv]

Post a small workbook with the named ranges and the formula

This will make it easier to see

I can try. Excel is not allowing me to even complete the entry of the formula. My only option is to read the error message ("the formula you typed contains an error") and two buttons, either OK or HELP). When I click OK, it takes me back to the cell in edit mode. At that point the only way out of edit mode is the ESCAPE key, which removes my formula and returns me to a blank cell. What I can do is include the formula without the "=" and display it as plain text. That would at least show you what I am attempting to enter.

To expand on my original question, my objective is to AVERAGE the daily figures on the first row for each day, leaving the remaining rows for that day blank. You will note that the actual formula includes the condition to accomplish that in addition to the AVERAGEIF part, which is what is causing the problem.

TEST.xlsx

[Paul_Hossler]

You can't use 'Day(Dates)' like that.

Even making it any array formula generates errors

Easiest thing to do would be a helper column and use that, or make your own UDF to do the averaging

Capture.JPG

[Opv]

Thanks. Looking at the revised sheet I realize it didn't previously dawn on me that October 1 would be averaged with September 1. It appears I need to shift focus away from the day and average instead by date. I'll play around with it some more using your suggestion. Thanks again.

[Paul_Hossler]

I'd use a pivot table (green), or possibly make a UDF (orange)

Capture.JPG

Note that row 14 is probably an error


There error checking and error handling that should be added before going to production with this

Option Explicit

Function DailyAverage(DatesIn As Range, DayIn As Date, DataIn As Range) As Variant
    Dim i As Long, n As Long
    Dim d As Double
    
    
    For i = 1 To DatesIn.Rows.Count
        If Int(DatesIn.Cells(i, 1).Value) = Int(DayIn) Then
            n = n + 1
            d = d + DataIn.Cells(i, 1).Value
        End If
    Next I
    
    
    DailyAverage = d / n
End Function

[Opv]

Thanks. I'll play around with those options.