I have two sheets one has a table with sets across the top of the table and daily km for each set the second sheet has set numbers in column b, I wish to find the daily average for the last 6 months (so 182 days) for each of the sets and return the value in column F.

Sheet 2

sheet 1 - Table

I have used the following formula to average the entire column =AVERAGE(INDEX(Table1[[H9003]:[H9043]],0,MATCH('FA Forecasts'!B5,Table1[[#Headers],[H9003]:[H9043]],0)))

However I only want to return the average of the last 182 rows of the table to the corresponding set number - and each time a new line is added I only want the last 182 rows.

I'm not sure what I can do to achieve this - any advice would be most appreciated.

CodePudding user response：

If you have Excel 365 you can use this formula:

=LET(indexSetNumber,MATCH([@[Set Number]],tblSheet1[#Headers],0),
filterByDate,FILTER(tblSheet1,tblSheet1[Date]>=LARGE(tblSheet1[Date],cntDays)),
filterBySet,INDEX(filterByDate,,indexSetNumber),
AVERAGE(filterBySet))

I like "readable" formulas - this one reads like this:

• indexSetNumber: returns the column index of e.g. H9003 --> 5
• filterByDate: returns the rows with Top "cntDays" dates. I am using a parameter cntDays in C2 - for this example I chose 2, but you would put 182.
• filterBySet: returns column indexSetNumber from filterByDate
• from these values AVERAGE is returned.

CodePudding user response：

This might also work (assuming C2 is =TODAY()):

=AVERAGEIF(Table1[Date],">" & C2 - F2,INDIRECT("Table1["& 'FA Forecasts'!B5 &"]"))

Using AVERAGEIF formula :

• Table1[Date] : returns the "Date" table column (sheet 1)
• ">" & C2 - F2 : "date more recent than Today-182 days"
• INDIRECT("Table1["& 'FA Forecasts'!B5 &"]") : returns the table column (sheet 1) which header match with Set Number in the current row (sheet 2)

Basically return the average for kms in the past 182 days for the Set Number of the row.