I’m cleaning and preparing school test score data for later this week. We are ranking schools for each subject and grade level based on average test score. The somewhat annoying challenge in excel is getting the built-in RANK() function to work on filtered data. When trying to input a range of scores into the RANK function, excel is including the hidden rows. The solution is courtesy of Damon Ostrander (yeah, he’s a pianist as well as a VBA pro).
Function Vis(Rin As Range) As Range Dim Cell As Range Application.Volatile Set Vis = Nothing For Each Cell In Rin If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then If Vis Is Nothing Then Set Vis = Cell Else Set Vis = Union(Vis, Cell) End If End If Next Cell End Function
I very very rarely mess with VBA (though I should find some time to learn it), but this was just too perfect. All you have to do with this function is go from =RANK(a1, $a$1:$a$99) to =RANK(Vis(a1), Vis($a$1:$a$99)). Drag the auto fill down the column and Presto!
As always, if you have any ideas or know better ways to do this, leave me a note in the comments.