Formula Help for checking order of list and ignoring Zero values

[minyaloth]

Hi, I hope someone can help.

I'm trying to build a tool to assist me with scheduling.

I've built a spreadsheet which has 3 columns of data
A - Rank
B - Name
C - Shifts

The rank is a numerical ranking and I assign a number to each name. The rank runs from 1 (most senior) to 20 (least senior).
The second column is employee names

The last column is number of shifts given.

Built like this, I can sort the employees by rank / seniority

Here's the rub:
I want to use column D to check that no employee with a lower rank has more shifts than one with a higher rank.
They can be equal however.

Another thing which has made this harder for me is that there are occasions where an employee may have 0 shifts
as they could be on leave or sick.


My thinking formula wise was to use nested if statements

D1: =IF(C1=0,"OFF",IF(AND(C1<=C2,C1<=C3,C1<=C4,C1<=C5,C1<=C6,C1<=C7,C1<=C8,C1<=C9,C1<=C10,C1<=C11,C1<=C12,C1<=C13,C1<=C14,C15<=C16,C1<=C17,C1<=C18,C1<=C19,C1<=C20),"GOOD","BAD"))

D10:=IF(C1=0,"OFF",IF(AND(C10<=C1,C10<=C2,C10<=C3,C10<=C4,C10<=C5,C10<=C6,C10<=C7,C10<=C8,C10<=C9,C10>=C11,C10>=C12,C10>=C13,C10>=C14,C10>=C15,C10>=C16,C10>=C17,C10>=C18,C10>=C19,C10>=C20),"GOOD","BAD"))

D20: =IF(C20=0,"OFF",IF(AND(C20<=C19,C20<=C18,C20<=C17,C20<=C16,C20<=C15,C20<=C14,C20<=C13,C20<=C12,C20<=C11,C20<=C10,C20<=C9,C20<=C8,C20<=C7,C20<=C6,C20<=C5,C20<=C4,C20<=C3,C20<=C2,C20<=C1),"GOOD","BAD"))

I realise this is clunky and also creates problems if the cells above or below are equal to 0.

I'm racking my mind trying to figure out a smart way to make this work but come come up with anything better
This is how Column d should work,

If there is an issue, then the relative cell in column D needs to display "BAD",
If however all shifts above are higher than or equal to,
and all shifts below are lower than or equal to
OR the shift in that cell equals 0, then it should display, "OFF"

 

[jasonb75]

Not sure if I'm following correctly from the description, see if this helps.

=IF(C1=0,"OFF",IF(COUNTIFS($A$1:$A$20,">"&A1,$C$1:$C$20,"<"&C1),"BAD","GOOD"))

 

[minyaloth]

Hi Jason

Thanks for the quick response,

I've attached a screenshot of my spreadsheet with your formula applied to the cells and the results

All the cells from D1:D19 that read, "BAD", should read, "GOOD"
The "OFFs" in that range are all correct.
And for some reason only D20 reads good.

I apologize for not explaining it more clearly.
Eg. Jennifer has a rank of 1 and therefore is the most senior person. No one below her should have more shifts, but they can be equal.
Jane has a rank of 2 and therefore is the second most senior person. No one below her should have more shifts, but they can be equal.
Etc...


Column D is to alert me to whether there is an issue, ie I gave a junior "MORE" shifts than a senior and to show me exactly where that issue is.

Then I would use one IF statement to confirm that all of column D = either OFF or GOOD and that would tell me that everything is in order (That formula is easy)

 

[jasonb75]

I had the logic in reverse, hopefully right this time

=IF(C1=0,"OFF",IF(COUNTIFS($A$1:$A$20,"<"&A1,$C$1:$C$20,"<"&C1,$C$1:$C$20,">0"),"BAD","GOOD"))

Then, to check the column,

=IF(COUNTIF($D$1:$D$20,"BAD"),"BAD","GOOD")

 

[minyaloth]

Hi Jason, this is perfect and does exactly what I was looking for.

Just out of curiosity, what logically is the IF statement doing? I really want to understand the logic to improve my understanding and abilities

 

[Fluff]

Cross posted Check that no employee with a lower rank has more shifts than one with a higher rank
and Help with formula

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Have you posted this on any other sites?

 

[minyaloth]

Hi, my bad, yes I have. Link attached, Check that no employee with a lower rank has more shifts than one with a higher rank