Search code examples
excelexcel-formularanking

If LARGE produces the same rank, THEN SPILL error

I have a static rank column. I'm ranking the companies in each category and then eventually on the grand total. this is dynamic as the scores are still coming in.

The issue that I'm running into is a SPILL "Error" (not really an error), when the rank returned from the companies would be the same. ie two companies have the same score therefore their rank is the same.

When two companies have the same score, creating the same rank I want the formula to simply refer to the Company's grand total for the tiebreak and list the one after or before the other.

This is the formula, that works great for most of the categories until we get to a Company that matches scores.

Can someone help add a condition to this for IF a tie, THEN rank them by Grand Total?

=FILTER($H$25:$H$30,$J$25:$J$30=LARGE($J$25:$J$30,$H16))

I've searched some other articles here on stack overflow and other platforms but have not seen a problem the same and molding those solutions to my problem have not worked out for me thus far.

Solution

  • Sort By Score, Then By Total

    Why not just spill the results without using the rank column?

    =SORTBY(H25:H30,J25:J30,-1,I25:I30,-1)

    enter image description here