1 Reply Latest reply on Dec 5, 2013 12:12 PM by Tracy Rodgers

    Apply Criteria to Filter Rows

    Michael Lance



      I have student test data with the following descriptive fields:

      Window (test window, i.e. Spring, 2012)

      Site_Name (school)

      Student ID


      and the following fields per subject area (Math, Reading, ELA, and Science):


      Standard Error (of measurement)


      and one strand score, which is a sub-score derived from the overall score.


      I want to be able to apply the following criteria in order to select the correct row per student (per test window, subject, and perhaps school):

      Select the score with the...

      1. Lowest SEM (standard error of measurement)

      2. If tied SEMs exist, most recent date

      3. If tied SEMs and dates exist, Fall: lowest score and Spring: highest score


      There are 2 tabs: one is for subject scores and the other is for strand scores.

      The subject tab will show the correct score to display if just the "1" is selected on the "use" column, as I have added this column to the Excel file showing which row to use per subject/student/window.

      The strand tab shows the strand scores associated with the subject scores, so if just the "1" is selected on the "use" filter, it will show the correctly associated strand score, but not the score selected itself (since that is at the subject level).


      Any help would be greatly appreciated.




        • 1. Re: Apply Criteria to Filter Rows
          Tracy Rodgers

          Hi Michael,


          I think you'll probably want to create a different worksheet for each subject and place them all on a dashboard together (use an Applied filter to have the filters apply across the different sheets). Then, create a calculation similar to the following:




          Place this on the color shelf, right click and have it computed by Student ID. That will show the lowest SEM. The calculation may get more complex with the dates.