I have a list of students and scores with the test sequence. I would like to be able to flag any test scores that have a grade of an F that are immediately following a grade of an A. I can filter to just scores of F, but am having a difficult time figuring out how to reference the score immediately before it.
The attached workbook has a tab called Grade Drop Flag that shows what I am trying to do.
Thanks for any help!
Hi Carrie,
In the attached workbook, I created a filter called "Lookup Filter" that returns rows either where the grade is F immediately following an A, or an A immediately preceding an F - this way it returns both rows in the combination for each pair.
Here's the Lookup Filter calculation:
if max([Letter Grade]) = "F" and lookup(max([Letter Grade]),-1) = "A"
or max([Letter Grade]) = "A" and lookup(max([Letter Grade]),1) = "F"
then "Include" else "Exclude"
end
When you put it on your filter shelf, make sure you set it to calculate using "Table (down)". I added this filter to the GRADE DROP (2) tab on your dashboard. When only the "Include" value is selected it returns the A and F pairs you're looking for.
Let me know if you have any questions.
Best,
Paul
