Rolling YTD average formula help - criteria range

josh_m

Board Regular
Joined
Mar 26, 2009
Messages
81
i have this formula which works to give me an average for an individual week (A3):
=IFERROR(AVERAGEIFS(wkly_recap_data_new!$P:$P,wkly_recap_data_new!$AF:$AF,A3,wkly_recap_data_new!$B:$B,A$1,wkly_recap_data_new!$AG:$AG,""),NA())​

in the next column i want to get a rolling YTD average, so as i run the formula down i would like it to look something like this (this does not work, but i think illustrates what i am after):
=IFERROR(AVERAGEIFS(wkly_recap_data_new!$P:$P,wkly_recap_data_new!$AF:$AF,A$3:A3,wkly_recap_data_new!$B:$B,A$1,wkly_recap_data_new!$AG:$AG,""),NA())​

then the next cell down would be:
=IFERROR(AVERAGEIFS(wkly_recap_data_new!$P:$P,wkly_recap_data_new!$AF:$AF,A$3:A4,wkly_recap_data_new!$B:$B,A$1,wkly_recap_data_new!$AG:$AG,""),NA())​

does anyone know how to modify the formula to accomplish this?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
11,529
Office Version
365, 2010
Platform
Windows, Mobile
Below is one way of applying a rolling year to an AVERAGEIFS, hopefully it will help you to apply it to your formula as I would prefer to see a layout of your data before committing to a full modification of your formula.

Excel Workbook
AIPAF
1Search dateRolling yearValuesDates
21601/12/2012
32525/12/2012
41307/01/2013
51920/10/2013
61026/11/2013
708/01/20141308/01/2014
Sheet1
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,155
Office Version
2019
Platform
Windows
i have this formula which works to give me an average for an individual week (A3):
=IFERROR(AVERAGEIFS(wkly_recap_data_new!$P:$P,wkly_recap_data_new!$AF:$AF,A3,wkly_recap_data_new!$B:$B,A$1,wkly_recap_data_new!$AG:$AG,""),NA())​
On the assumption that the formula I've quoted from your post is in B3, try using

=AVERAGE(B$3:B3)
 

josh_m

Board Regular
Joined
Mar 26, 2009
Messages
81
thanks, i thought of that also, but it technically gives an average of the averages which is close but won't be the true average of the entire range
 

josh_m

Board Regular
Joined
Mar 26, 2009
Messages
81
i may have to end up going that route, with the average of averages though, appreciate it
 
Last edited:

josh_m

Board Regular
Joined
Mar 26, 2009
Messages
81
thanks, unfortunately the date data is not in the standard mm/dd/yyyy format. the column with the date info is given in this format: 01-01, 01-02...02-01. which represent our "fiscal month - fiscal week"
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,155
Office Version
2019
Platform
Windows
thanks, i thought of that also, but it technically gives an average of the averages which is close but won't be the true average of the entire range
Could you provide a small set of sample values where there is a difference between the averages?

To the best of my knowledge there are only 2 things that could cause any slight variance in the results:-

- Rounding the individual averages. Which is eliminated as there is no rounding function in your first formula.
- Setting 'precision as displayed' in excel options.

Beyond that, I believe that the results should be correct.

If we do need to look at an alternative method, we would need to know the cell format of your fiscal dates and your regional date format.
 

josh_m

Board Regular
Joined
Mar 26, 2009
Messages
81
Could you provide a small set of sample values where there is a difference between the averages?

To the best of my knowledge there are only 2 things that could cause any slight variance in the results:-

- Rounding the individual averages. Which is eliminated as there is no rounding function in your first formula.
- Setting 'precision as displayed' in excel options.

Beyond that, I believe that the results should be correct.

If we do need to look at an alternative method, we would need to know the cell format of your fiscal dates and your regional date format.

i think you may be correct about an option in excel, auto-rounding or something (not sure) the results of the formula are like 6.987654456, but when i take the average of the averages the results just go out a few decimal placet like, 6.9877000000

anyhow, i found an ugly way to calculate the avg. without using the "average" function. it works, but its not pretty and i have to manually edit each cell's formula.

=SUM(SUMIFS(wkly_recap_data_new!$P:$P,wkly_recap_data_new!$B:$B,$A$1,wkly_recap_data_new!$AG:$AG,"",wkly_recap_data_new!$AF:$AF,{"01-01","01-02","02-01"}))/SUM(COUNTIFS(wkly_recap_data_new!$B:$B,$A$1,wkly_recap_data_new!$AG:$AG,"",wkly_recap_data_new!$AF:$AF,{"01-01","01-02","02-01"}))

thank you so much for helping, i really appreciate all the help i have received on this board. it's amazing
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,155
Office Version
2019
Platform
Windows
Are the dates in wkly_recap_data_new!AF:AF formatted as text?

If not this might work

=IFERROR(AVERAGEIFS(wkly_recap_data_new!$P:$P,wkly_recap_data_new!$AF:$AF,"<="&A3,wkly_recap_data_new!$B:$B,A$1,wkly_recap_data_new!$AG:$AG,""),NA())

Are your normal dates in UK or US format?
 

josh_m

Board Regular
Joined
Mar 26, 2009
Messages
81
Are the dates in wkly_recap_data_new!AF:AF formatted as text?

If not this might work

=IFERROR(AVERAGEIFS(wkly_recap_data_new!$P:$P,wkly_recap_data_new!$AF:$AF,"<="&A3,wkly_recap_data_new!$B:$B,A$1,wkly_recap_data_new!$AG:$AG,""),NA())

Are your normal dates in UK or US format?
to answer your question, i'm in US. so I showed my work and report to my boss and he thought it was too much data for a summary report and decided he wanted everything rolled up into monthly buckets rather than weekly...so my original problem is moot now...grrrr! frustrating. anyhow, they way he wants it now will be much simpler to put together.
 

Forum statistics

Threads
1,082,478
Messages
5,365,783
Members
400,850
Latest member
Raj_Jpr

Some videos you may like

This Week's Hot Topics

Top