2 Replies Latest reply on May 11, 2018 6:09 AM by Jennifer Leo

# Top N with ties for a set

Hello all,

Need some help with creating a set that uses Top N. I need this Top N to be able to include ties and the reason I used top N in a Set rather than a table calc is because the results of this Top N Set is needed for a different calculation on another sheet. This other sheet is using a series of complicated calculations, one of which is determining whether or not a question fell into the Top N (among several other criteria). In other words, I can't really use a table calculation (such as ranking) because the result is needed for an entirely different sheet with almost none of the data that would be relevant to the table calculation on that second sheet. Alternatively, is there a way to do ranking without using a table calculation?

If it's any help this is pivoted data in which I am ranking the scores of a list of questions that were rated on a 1 - 5 scale.

Again, to clarify my end goals is to have some sort of means of figuring out the Top 3 (that will including more than 3 if the third item is tied with others), the results of which can then be used within several layered calculations for another sheet entirely.

Any thoughts on how one might accomplish this?

Unfortunately, I cannot connect a workbook due to confidentiality, but again my version is 10.5.3.

• ###### 1. Re: Top N with ties for a set

Hi Leo,

hope it helps you.

BR,

bharat

• ###### 2. Re: Top N with ties for a set

All of these are very interesting and helpful for creating sets with Top Ns and/or ranking. However (unless I am missing something), this doesn't seem to address my larger question. The end result I need is a Top 3 (that recognizes dense ties) the result of which can be used in a series of calculated fields for a different sheet entirely. Here are the methods I've tried (and the problems that resulted):

1.) I can create a Set using Top Ns (limited to 3) to use for a calculation. I can then use this calculation in my other layered calculations. What I can't do however is, is set it up so that it recognizes ties within that Top 3 (so that, say if the third and fourth one are tied, both are considered members of that Set).

2.) Alternatively, I can use a Rank table calculation so that I can retain ties (specifically dense ties), but because it is a table calculation I can't use the result of that rank table calculation in the calculations necessary for my other sheet (because that data doesn't exist in the that form for the table calculation to run properly in that second sheet).

So, is there a way to either a) get ties to be included within a Top N Set or b) a way to do ranking without using a table calculation (so that the result can be used in calculations for a different sheet with a different set up)? Or, is there another way entirely to achieve what I am looking for that I just haven't thought of?