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 © 2024 vBulletin Solutions Inc. All rights reserved.