Create Ranking/List via Conditions

[iilman92]

Hi Everyone. I'm currently working on a project and looking to see if what I'm trying to accomplish is feasible within Excel or not. What I'm attempting to do is create a Ranking List which will determine priority of our open positions based on several factors. This may not work as a formula so if not, maybe a dashboard of some sort?

Screenshot 2024-05-30 113559.png

The factors I am dealing with are within columns A-G in the screenshot and attachment.
If the store's EBITDAR < 95%
The Job Rating (1-3)
# of Positions Open
# of Days Open (per Position by Rating, High to Low)

What I'm trying to do is create a formula or rank list that will take the current store's EBITDAR % and if it is less than 95% (create a ranking), use that as the first factor.
If the store has less than 95% EBITDAR, I would then like to identify the Job Title's of the Positions Open by Rating as the second factor. Then identify the # of positions at the stores that have less than 95% EBITDAR to determine vacancy, in addition to determining priority in conjunction with # of Days Open.

As an example using data from H-M....
Screenshot 2.png

[First Factor] Sorting EBITDAR Low-High
[Second Factor] Job Title Rankings from 1-3
[Third Factor] # of Positions Open by Rating
[Fourth Factor] # of Positions Open by Days Open

I see that Stores 2009, 1178, 3002 and 3022 all have a position with a #1 Rating. Since stores 3002 & 3022 have the highest Vacancy (# of Open Positions), this would take precedence over stores 2009 & 1178. Since the Assistant Manager at Store 3002 has been open longer than the Store Manager at Store 3022, the order for the priority for the #1 Positions would go as follows:

Store 3002 - Assistant Manager
Store 3022 - Store Manager
Store 2009 - Store Manager
Store 1178 - Assistant Manager

Is this something that is even possible via a formula? Or will I basically just need to make a dashboard that lists the appopriate factors?

[AliGW]

Try pasting this into P2 and ENTER:

Please Login or Register  to view this content.

It certainly gets the four you mention in the right order, however to do this, the ranking factors have to take a different order of preference.

Please check the attached and annotate where items are incorrectly ranked.

[iilman92]

Try pasting this into P2 and ENTER:

Please Login or Register  to view this content.

It certainly gets the four you mention in the right order, however to do this, the ranking factors have to take a different order of preference.

Please check the attached and annotate where items are incorrectly ranked.

Hi Ali,

This looks wonderful. I'm trying to reverse engineer and understand the formula you wrote.

in "=LET(f,FILTER"", is the "f" indicating column F as the # of positions open as the first factor? Or what is "f"? Sorry for the ignorance.

If so, what I'm seeing is: H2-M199 is filtered to show all under 95% EBITDAR, then it sorts by (columns H-M), Rating (4), Open (2), Days Open (6). Is this correct?

The only thing I noticed was that in the Array Results, the EBITDAR isn't sorted Low-High within the information, is it possible to do so in this order after the Array results?

For example per the below, in the Rating 1 Jobs for 2 Vacancy's the EBITDAR's with negative value are at the end of the results rather than being listed higher for having a lower EBITDAR. Is there anyway to rewrite the formula to FILTER<95% then sort by EBITDAR Low to High, followed by being sorted by Rating, Open, Days open?

Example attached:
Screenshot 2.png

[JeteMc]

Perhaps this Power Query proposal will work for you.
After converting the data in $H$1:$M$199 into an Excel table and selecting From Table/Range on the Data tab the following Advanced Editor code is applied:

Please Login or Register  to view this content.

The resulting table of output is shown in columns O:T.
Let us know if you have any questions.