Yet another RANKEQ question! :)

Jesse Sorrells

Hello all!

I've been diving into Smartsheets, and more recently Dashboards since taking on my new role in Quality Assurance and have come to a roadblock.

Goal: I'm attempting to pull a "Top 5 error offenders for yesterday" to display on my Quality Dashboard so it updates automatically.

I have a column ("Errors Yesterday") that counts the number of occurrences a team member had yesterday.

=COUNTIFS([Error User]:[Error User], [Error User]@row, [Error Date]:[Error Date], TODAY(-1))

I have attempted to use RANKEQ to rank the "Errors Yesterday" column but it doesn't seem to be returning the correct results.

=RANKEQ([Errors Yesterday]@row, [Errors Yesterday]:[Errors Yesterday], 1)

The plan would then be to have my metrics sheet pull the #1,2,3 for yesterday to a bar graph on another dashboard.

I currently have to manually enter the top daily/weekly/monthly error offenders but am trying to automate it.

Let me know what other information I can provide to assist.

Thank you in advance for your help!

-Jesse

Mark Cronk

Hi @Jesse Sorrells ,

Have you tried using LARGE() instead of RANKEQ?

https://help.smartsheet.com/function/large

Mark

Jesse Sorrells

Thank you Mark for your quick reply!

I haven't tried using LARGE() but I've made progress with the RANKEQ function.

Current Statement:

=IF([Error User]@row = "", "", IF([Errors Yesterday]@row = "", "", RANKEQ([Errors Yesterday]@row, [Errors Yesterday]:[Errors Yesterday], 0)))

This is now returning correct rankings and cleans it up a bit if there's missing data

Current Issue:

Team members that are have multiple errors are have duplicated ranks and causing it to skip some ranks as well.

Idea would be to use a VLOOKUP for Rank 1-3 and Display the Error Users with the most occurrences.

Thanks again for all of your help!

-Jesse

Mark Cronk

Hi @Jesse Sorrells ,

Now that I've looked more closely, it appears you're trying to create summary metrics in the rows of your main data sheet. Is that what you're doing? If so, an easier solution would be to create a separate metrics sheet to determine ranks and provide the data for charts. It will help get you past the problems you're having now with many rows that contain the same data. Happy to help you with that if you think it will work. I'm only seeing a couple screen shots of your entire workspace.

Mark

Jesse Sorrells

I do currently have a metrics sheet that my dashboard pulls from but maybe I'm not utilizing it properly.

This is one of the simpler metric sheets where I planned on using the VLOOKUP function to pull the team members name and qty of errors based on rank.

If there's an easier way to accomplish this I'd love some help.

Again, thank you for your quick replies!

Jesse Sorrells

Ok I can't get RANKEQ to work.

I tried using the LARGE function and I can get a standard function to work but I only need it to return the largest 3 error amounts within a certain date.

I would use this formula in my metrics sheet to pull the "Top 3 Error Users (by qty of occurrences) for Yesterday" or TODAY(-1)

to then have my Dashboard pull the info.

Thank you again for all of your help!

Mark Cronk

Hi Jesse,

I'm having a little trouble following your various screenshots but will take a stab at it.

In place of your vloolup on the metrics sheet, try using:

=JOIN(COLLECT({Insert your user range}, {insert your errors yesterday range}, =LARGE({insert your errors yesterday range}, Category@row)), "-") ...... where category@row is 1, 2 ,or 3 and the ranges are pulling from the same external sheet.

This should return the user name of the person with the 1st, 2nd, 3rd highest errors yesterday. If multiple people have the same number of errors it will join their names with a "-" between them. You can change the delimiter in the formula if you'd like. If there is only 1 person it will return just their name.

Did I get lucky?

Mark