View Full Version : [SOLVED] Issue with AVERAGEIF

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

12-05-2016, 08:13 AM

Post a small workbook with the named ranges and the formula

This will make it easier to see

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.

17757

Paul_Hossler

12-05-2016, 09:25 AM

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

17759

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

12-05-2016, 10:00 AM

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

17760

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

Thanks. I'll play around with those options.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.