RANKAVG Function - exclude cell from range

jet2clemson

Hello,

I am trying to use the RANKAVG function but cannot figure out how to exclude a cell from the range selection. My equation is: =IFERROR(RANKAVG([Total Score]@row, [Total Score]$12:[Total Score]$77, 0), ""). I do not want to include cells [Total Score]24:[Total Score]26, [Total Score]39:[Total Score]41, [Total Score]52:[Total Score]54, or [Total Score]65:[Total Score]67 in the selection. Is there a way to exclude these cells from the ranking?

Paul Newcome

I suggest creating a helper column that can be hidden later. Using a checkbox column, you can check the box for all rows you want to include and include the range/criteria in your formula by incorporating a COLLECT function in place of the range.

Paul Newcome

I suggest creating a helper column that can be hidden later. Using a checkbox column, you can check the box for all rows you want to include and include the range/criteria in your formula by incorporating a COLLECT function in place of the range.

jet2clemson

Got it! Thank you!

=IFERROR(RANKAVG([Total Score]@row, COLLECT([Total Score]$12:[Total Score]$77, DNS$12:DNS$77, 0), 0), "")

Paul Newcome

Perfect. Glad you got it working. Happy to help! 👍️