RANKEQ formula returning 1 for all rows

System

This discussion was created from comments split from: Help with ranking based on criteria (RANKEQ, COLLECT).

SkiPatrolScott

Hey @Genevieve P. I have tried both of these 'rank' formulas but the Rank comes back as 1? I have used variations of these in other sheets with success so I am confused (very often) as to what I am missing? The Violations Cnt is being inputted via another sheet, if that makes any difference…

=RANKEQ([Violations Cnt]@row, COLLECT([Violations Cnt]:[Violations Cnt], [Primary Column]:[Primary Column], [Primary Column]@row), 0)

=IF([Primary Column]@row = "", "", RANKEQ([Violations Cnt]@row, COLLECT([Violations Cnt]:[Violations Cnt], [Primary Column]:[Primary Column], [Primary Column]@row), 0))

Paul Newcome

Try getting rid of the COLLECT function and just using the [Violations Cnt]:[Violations Cnt] as your range.

Using the COLLECT function to restrict the range for your RANKQ based on the [Primary Column] means you are collecting only a single row to rank your count in because each entry in the [Primary Column] is unique. You are essentially saying to rank [Violations Cnt]@row within [Violations Cnt]@row.