AVERAGEIFS using Date rage as a criteria.

[mark hansen]

A few days ago I found on the board the AVERAGEIFS function and I’ve been able to get to work for some things. When I add additional criteria it doesn’t work. We are on Excel 2007.

Here is was I have so far.
=IFERROR(AVERAGEIFS(Data_Input!$X$6:$X$1000000,Data_Input!$P$6:$P$1000000,"=X",Data_Input!$C$6:$C$1000000,C18),"No Data")

In the Data input tab I have a table containing a few key columns:
Date (formatted to MMM-YY) (Column B )
Person (Column C)
Section (Column D)
A “flag” to indicate the scores also count against a certain problem
3 different scores (Columns X, Y, Z)

The formula it’s self could probably be better, I assume I don’t need to do down to row 1,000,000 but as the table will be building (doing down) over time, I didn’t want to need to change the formulas. I put it inside the IFERROR function to include the “No data” of the person doesn’t have any scores within the date range.

On a Summary tab, I have the person’s name listed and the AGERAGEIFS formulas to average the score for the that person. This works well.

I need to only calculate the average scores for a certain date range. I include the date range, a beginning and ending date (also formatted to MMM-YY). When I included the additional criteria of > beginning date (summary sheet E6) and < ending date summary Sheet E7, the formula doesn’t work. My intent is, as the data is entered over time, My summary sheet will only look at the date range reflected in the beginning and ending dates and average the scores within that date.

Do I have to treat the date range criteria differently than the person’s name, or the section or the problem flag? Do I have to use square brackets (an array formula, I don’t know how to do that.)

Thanks for any help.
Mark

 

[T. Valko]

A few days ago I found on the board the AVERAGEIFS function and I’ve been able to get to work for some things. When I add additional criteria it doesn’t work. We are on Excel 2007.

Here is was I have so far.
=IFERROR(AVERAGEIFS(Data_Input!$X$6:$X$1000000,Data_Input!$P$6:$P$1000000,"=X",Data_Input!$C$6:$C$1000000,C18),"No Data")

In the Data input tab I have a table containing a few key columns:
Date (formatted to MMM-YY) (Column B )
Person (Column C)
Section (Column D)
A “flag” to indicate the scores also count against a certain problem
3 different scores (Columns X, Y, Z)

The formula it’s self could probably be better, I assume I don’t need to do down to row 1,000,000 but as the table will be building (doing down) over time, I didn’t want to need to change the formulas. I put it inside the IFERROR function to include the “No data” of the person doesn’t have any scores within the date range.

On a Summary tab, I have the person’s name listed and the AGERAGEIFS formulas to average the score for the that person. This works well.

I need to only calculate the average scores for a certain date range. I include the date range, a beginning and ending date (also formatted to MMM-YY). When I included the additional criteria of > beginning date (summary sheet E6) and < ending date summary Sheet E7, the formula doesn’t work. My intent is, as the data is entered over time, My summary sheet will only look at the date range reflected in the beginning and ending dates and average the scores within that date.

Do I have to treat the date range criteria differently than the person’s name, or the section or the problem flag? Do I have to use square brackets (an array formula, I don’t know how to do that.)

Thanks for any help.
Mark

Try it like this...

A1 = lower date boundary
B1 = upper date boundary

=IFERROR(AVERAGEIFS(Data_Input!$X$6:$X$1000000,Data_Input!$P$6:$P$1000000,"=X",Data_Input!$C$6:$C$1000000,C18,Data_Input!$B$6:$B$1000000,">="&A1,Data_Input!$B$6:$B$1000000,"<="&B1),"No Data")

 

[mark hansen]

Thanks for the quick reply. In my searches on the internet, I didn't hit any sites saying use the "&" between the "<=" and the Cell reference.

I had a little problem in my end with the dates. (I know the MVPs on the board know this, but maybe not everyone...

I didn't realize even if I formated the cell to MMM-YY that was just the display, the date in the cell was a specific date. It was messing with the results because of the day that was contained in the cell. Once I figured out that one I had not problem.

It there better way to get by this? I fixed it by normalizing the date to the beginning date to the first of the month =DATE(YEAR(E6),MONTH(E6),1) and the ending I changed to the first day of the next month =DATE(YEAR(E7),MONTH(E7)+1,1). the user puts in the dated in E6 and E7 and I pull from another cell for the formula.

I have another question regarding the cells I'm getting the percentages and if there was a way to get around having a formula going down to row 1,000,000 which of course makes the spreadsheet way to big, but that will be another string.


Thanks again T. Valko

 

[T. Valko]

Thanks for the quick reply. In my searches on the internet, I didn't hit any sites saying use the "&" between the "<=" and the Cell reference.

I had a little problem in my end with the dates. (I know the MVPs on the board know this, but maybe not everyone...

I didn't realize even if I formated the cell to MMM-YY that was just the display, the date in the cell was a specific date. It was messing with the results because of the day that was contained in the cell. Once I figured out that one I had not problem.

It there better way to get by this? I fixed it by normalizing the date to the beginning date to the first of the month =DATE(YEAR(E6),MONTH(E6),1) and the ending I changed to the first day of the next month =DATE(YEAR(E7),MONTH(E7)+1,1). the user puts in the dated in E6 and E7 and I pull from another cell for the formula.

I have another question regarding the cells I'm getting the percentages and if there was a way to get around having a formula going down to row 1,000,000 which of course makes the spreadsheet way to big, but that will be another string.


Thanks again T. Valko

Not too much you can do about the date thing and keep the formula efficient. If the date range is for a specific full month period you could test for mmm-yyyy however, you'd need to use a different formula which is not as efficient as the one you're currently using.

As far as referencing down to row 1,000,000....

That's really not a problem with the formula you're using. The AVRAGEIFS function will only calculate within the used range.

If you have data down to row 10,000 the formula will only evaluate range references down to row 10,000 even though you may reference down to row 1,000,000.

Excel is kind of "smart" about this. That's what keeps it from taking forever to calculate.

However, array formulas will evaluate EVERY cell referenced regardless of the used range.