I am working on a spreadsheet with multiple sheets (excel 2010)
The first sheet the user puts all the data. In the tables(MainData) first column it lists the months, then a few metrics:
example
Month Accuracy Resolution Rate
Jan 2018 98% 99%
Feb 2018 99% 98%
Mar 2018 100% 99%
etc...
The second sheet is kind of a summary sheet for the user. They select a month from a drop down and they are shown that months metrics as well as the 12 month average for that month with the previous 11 months.
I have the formula to get the average, however it only works if I enter it on the same page as the data and in an adjacent column:
=AVERAGE([@Accuracy]:INDEX([Accuracy],MAX((ROW()-ROW(MainData[[#Headers],[Accuracy]]))-11,1),1))
My question is what is a formula to calculate and pull over the average to the second page based on the month the user selects?
Thanks!
Bookmarks