Rolling YTD average formula help - criteria range

[josh_m]

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]

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
	A	I	P	AF
1	Search date	Rolling year	Values	Dates
2			16	01/12/2012
3			25	25/12/2012
4			13	07/01/2013
5			19	20/10/2013
6			10	26/11/2013
7	08/01/2014	14	13	08/01/2014
Sheet1

 

[jasonb75]

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]

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]

i may have to end up going that route, with the average of averages though, appreciate it

 

[josh_m]

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]

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]

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]

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]

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.