Check that no employee with a lower rank has more shifts than one with a higher rank

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.

I'm attaching a rough spreadsheet to show what I'm doing.

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"

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new here, I have done it for you this time.)

Hey, I got an answer from another forum which perfectly achieved what I was looking for. Thank you

Please read this forum’s rules on cross-posting before posting here again, in particular rule #3.