I have a data which looks something like this (given below). This sheet is evergrowing with data added on a weekly basis.
A B C D
Name Stat1 Stat2 Data
Cint 0 2 234
Roger 3 5 262
Flash 3 4 190
Roger 3 5 272
Zach 2 3 269
Zach 2 5 272
I have another sheet with all the names and criteria for stat 1 and stat 2 in a cell lets say stat 1 = A1 and Stat 2 = B1
I want to write a formula that will look at stat1 and stat 2 criteria for each name and bring in the average of top 5 values.
The closest I have come so far skips the first line in the filter. So always my top value gets ignored.
=daverage(sortn(filter('Sheet2'!A1:D1000,('Sheet!A1:A1000=A5),('Sheet2'!B1:B1000<=A1),('Sheet2'!C1:C1000>=B1)),5,0,4,false),4,Z1:Z2)
A5 = Name to be looked for
A1 = stat 1
B1 = Stat 2
Z1:Z2 - I could not figure out how to use the criteria in daverage but choosing any random value for criteria (blank cells)
With this formula for example name Roger has top values 300, 290, 280, 270, 260 for which the average is 280. I am getting average as 275 as it skips the first number 300 (probably thinks that it is a header)
What am I doing wrong. Any help is really appreciated.
Bookmarks