I have 3 dimension matrix with Year, Data, Company categories.
Year is simply 2000, 2001, 2002 etc. The Data category has some data input rows and some calculated rows.
For example, one calculated row is the average for the previous 5 years of an input row.
This part works fine using running average techniques.
I want to have a second, summary, matrix that captures some of the calculated rows, e.g. the latest 5 year average.
The tricky part is I only want it for the last year with actual data in the Data input cells. Not all companies have data for the most recent year(s). So I want to see the last known 5 year average for each company.
For example, Company A might have data for all years, but Company B doesn’t yet have data for 2011 & 2012. Company C might not have 2009-2012.
This formula partially works.
Summary::Latest Ave = DataTable::5YrAve:Year[Last]
This works for Company A. But it picks up #Div/0 error for Company B & C because the last year (2012) doesn’t yet have proper data to calculate an average.
Anyone know how to do this?