Combo Nested Formula: Rolling Avg and Column Letter Calculation needed please

[trankim]

Is there a way for me to combine two of these formulas into one?

I have a worksheeet called "Products" that has a rolling average calculation based on the input number value. So for example, if I want to look at 4 weeks rolling average, I just put 4 in AK4 and the Rolling average knows to pull only 8 weeks worth of data from Columns AN - BG.

I also have another worksheet called "Executive Summary" that basically looks at a date and the formula will then go to the "Products" tab and match the date and then returns the column Letter for that date.

What I need help with: I have a combo filter box that has the dates from the Products tab. I would like to calculate the rolling average of 4 weeks based on the dates that is selected on the Combo Filter. Here's another possible problem....if someone selected May 13 for example, you wouldn't be able to calculate the average b/c there's no data for the first 3 weeks prior, so maybe the formula can return a "N/A".

Thank you in advance for anyone's help.

Rolling Average Formula: =IFERROR(IF($A14="","",AVERAGE(OFFSET($AN14,0,COUNT($AN14:$BG14)-AK$1,1,AK$1))),0)

Column Letter Forumula: =SUBSTITUTE(ADDRESS(1,SUMPRODUCT((Products!$AN$3:$ BH$3='Executive Summary'!$G$9)*(COLUMN(Products!$AN$3:$BH$3))),4),"1","")


Dates on the Products tab: May 6 would be where column AN starts.
6-May 13-May 20-May 27-May 3-Jun 10-Jun 17-Jun 24-Jun 1-Jul 8-Jul 15-Jul 22-Jul 29-Jul 5-Aug 12-Aug 19-Aug 26-Aug 2-Sep 9-Sep 16-Sep 23-Sep

[Admin]

Hi

=IFERROR(IF($A14="","",AVERAGE(OFFSET($AN14,0,AK2-AK1,1,AK$1))),0)

where AK2 is the linked cell from your drop down (form control)

if it's activex, get the date column index using

=match(ak2,an3:bg3)

and replace the highlighted range in the formula with this formula cell.

[trankim]

Thank you that formula worked beautifully. However, if someone selected May 6, the formula gives me a circular error...Any way we can prevent that? Thanks so much for your additional help

[Admin]

what's the formula and in which cell ?

[trankim]

The circular reference is in AK14

=IFERROR(IF($A14="","",AVERAGE(OFFSET($AN14,0,MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1,1,AK$1))),0)

[Admin]

try

=IFERROR(IF($A14="","",if(MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1>0,AVERAGE(OFFSET($AN14,0,MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1,1,AK$1))),0),0)

[trankim]

GENIUS. IT WORKS! I am over the moon. Thanks so much. Question, if the May 6 date was picked, the result shows "FALSE"....Is there any way that I can have it show "N/A" instead? This will be displayed on a scorecard....and "false" looks odd. Thanks so much again.

[trankim]

One last question, the end result is off by one week. For example, if 9/16, the results starts counting from 9/16 and 4 previous weeks (which is 8/26, 9/2, 9/9, 9/16). Is there a way where the formula would actually start to count the week before? So if someone chose 9/16, the rolling 4 week average would include weeks of 8/18, 8/26, 9/2, 9/9? It would not include the 9/16 results...make sense?

Sorry if I am making it a little more complicated, but the original formula above did this....Thank you in advance for your help. I TRULY appreciate this.

[Admin]

OK. it should be

=IFERROR(IF($A14="","",IF(MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1>0,AVERAGE(OFFSET($AN14,0,MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1,1,AK$1)),NA())),0)

Edit: just saw the additional requirement.

See if this help.

=IFERROR(IF($A14="","",IF(MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1>0,AVERAGE(OFFSET($AN14,0,MATCH('Executive Summary'!$H$9,$AN$3:$BG$3) -$AK$1-1,1,AK$1)),NA())),0)

[trankim]

OMG, you are super genius. IT WORKED> whew...thank thank you sooo much!

[Admin]

You are very welcome :cheers:

Share about this forum among your friends :)

[trankim]

absolutely!!!