Unable to RANK from Lowest Amount When the Item Is Only One

jspascual

Hello, 

 

I hope you're all doing well.

 

I have this problem that's preventing me from proceeding on to the next steps. 

 

I'm trying to rank the basic salary from lowest to highest, by position. 

As we all know, even if employees have the same position, there are instances that they don't have the same salary.

The idea is, I need to get the lowest salary per position. 

The issue I'm encountering is that, if I do a rank formula, it does return the correct order when there are more than 1 employees. 

But if the position is assigned to only 1 employee, meaning only 1 employee has that position, it always returns 2. I need the value to be 1 since that will be my basis of getting the salary amount: IF Rank = 1 THEN Basic Salary ELSE 0. 

 

Thank you for all the inputs you may share.

 

Regards,

 

Jonathan

jspascual

Sending an update.

 

Thank you for your reply, @jasonblinn 

It gave way for me to think further. 

I think this has been addressed:

1. I created a step line item that would rank all of the employees' salary 

2. Then another line item that ranks the step 1 above by position title. 

I only needed the '1' lowest per position title and gave me that for both 1 employee or more than 1 employees assigned to a role. 

Thank you again!

 

Jonathan

 

jspascual

This is the formula I'm using:

 

RANK(Basic Salary, ASCENDING, SEQUENTIAL, ISFIRSTOCCURRENCE, Position Title)

 

I've tried MAXIMUM, AVERAGE, MINIMUM in place of SEQUENTIAL, it did not work.

 

 

jspascual

Here's the excel equivalent of my module. Just to give a picture of what my module is. I hope this helps you in helping me... Thank you very much...

jasonblinn

@jspascual 

What is the Isfirstoccurrence doing in your formula? It seems to me that you would want to include the entire population in the ranking? 

 

I have mimicked what you are trying to do it and I think it is working per your question: (1-20 are employees, and Letters are the jobs/positions)

 

My Formula is: RANK(Salary, ASCENDING, MINIMUM, TRUE, Position)

 

When I sort it, you can see that they are displaying in order, and it is displaying 1 if there is only one person in that position. 

 

Hope this helps!

Jason

 

 

jspascual

Thank you, @jasonblinn 

 

I tried the formula but it is not ranking it by the position title. It is pulling their actual numbers from the entire employee list.

 

I probably missed out an important information on my previous posts. 

There is a dimension at the top  for the Position Title as well. Please see attached screen shot. 

 

 

Thanks much!

 

Jonathan