Marking Top 10 in Criteria List

[Jezzzza]

Hi,

I'm trying to find and mark the top 10 values in a large data set based on criteria, however I want the formula to simply return, "Yes" if it is in the top 10, and "" nothing if it is not.

My data set is revenue figures including revenue forecast, revenue actuals, and variance. It includes projects from multiple departments and I need to find the worst performing projects in each department based on the variance column.

I know about the RANK formula but this does not get me the desired results as it needs criteria.

Ideally I can then use this "Yes" in top 10 formula column as a filter in a pivot table to only pull in the worst 10 and then sort by department, descending, etc.

For this example:
Column I = Department used as criterium
Column P = Needed formula showing Yes in top ten
Column N = Value used as main criterium and ranking in descending order of worst to best performers

Every screenshot I have tried to post gives me an error being too large.

Thanks

 

[Jezzzza]

Hi,

I figured out the needed formula if anyone comes across this and is curious.


=IF(AND(IF([@[In Both System ]]="Yes",(COUNTIFS(I:I,[@[Project NetSuite Project Service Line]],N:N,">"&[@Difference],O:O,"Yes")+1),"")>=1,IF([@[In Both System ]]="Yes",(COUNTIFS(I:I,[@[Project NetSuite Project Service Line]],N:N,">"&[@Difference],O:O,"Yes")+1),"")<=10),"Yes","No")