# Rolling YTD average formula help - criteria range

#### josh_m

##### Board Regular
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?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### MARK858

##### MrExcel MVP
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/2014141308/01/2014
Sheet1

#### jasonb75

##### Well-known Member
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
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

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

Last edited:

#### josh_m

##### Board Regular
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

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
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
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
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.

Replies
4
Views
326
Replies
12
Views
235
Replies
0
Views
56
Replies
3
Views
121
Replies
4
Views
242