Formula for average of dynamic table

[Iris Stein]

Hi,

I would appreciate anyone's help here. I have set up a dashboard using figures that sales sends me on a semi-daily basis. For some days I have actual figures (when sales sends them) and for the days that I don't have actual figures, I enter the average sales amount.

I am trying to automate the excel table to take the actual sales figure when it exists and when it doesn't I would like it to take the average of the last seven days for which there is actual data.

How do I create a formula that would recognize which cells to take the information from - especially since the cells change on a daily basis and an additional row is added every day. Is there a formula that can take this into consideration.

I have attached a sample file for reference.

 

[Asheesh]

To keep it simple - You mean, you would enter the data manually once you have the actual sales figures...and if you dont have actual figures thenyou want to input the formula to caculate the average of last 7 days which have actual data..

Is my understaing of your problem correct?

 

[Iris Stein]

The sales figures automatically update as they are linked to a file. I want a formula which (like an IF formula) which when the sales is 0 - the average for the past seven reported days is placed. So yes, if I don't have actual figures, I want the formula to calculate the average of the last 7 days which has data.

 

[Asheesh]

Use the below formula along with If condition..this is an array formula and must be entered using CTRL + SHIFT + ENTER

Created a Helper column A..

AVERAGE(IF(IFERROR(IF(INDIRECT("A2:A"&COUNTA(MyTbl[Date])+1)="Actual",ROW(INDIRECT("1:"&COUNTA(MyTbl[Date]))),"")=LARGE(IF(INDIRECT("A2:A"&COUNTA(MyTbl[Date])+1)="Actual",ROW(INDIRECT("1:"&COUNTA(MyTbl[Date]))),""),{1,2,3,4,5,6,7}),0),INDIRECT(ADDRESS(2,COLUMN())&":"&ADDRESS(COUNTA(MyTbl[Date])+1,COLUMN()))))

See the attached..

 

[XOR LX]

Hi.

If there are any zeroes within a given column, it would appear that they are always in consecutive cells and always at the end of the column. Is this a correct assumption?

If so, to get the average of the last 7 non-zero entries, you can simply use, e.g. for column E:

=AVERAGE(INDEX(E2:E84,MATCH(0,E2:E84,0)-1):INDEX(E2:E84,MATCH(0,E2:E84,0)-7))

Regards