# Formula Help for checking order of list and ignoring Zero values

#### minyaloth

##### New Member
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

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"

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### jasonb75

##### Well-known Member
Not sure if I'm following correctly from the description, see if this helps.

#### minyaloth

##### New Member
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

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)

#### Attachments

• Capture.PNG
80.7 KB · Views: 2

#### jasonb75

##### Well-known Member
I had the logic in reverse, hopefully right this time

Then, to check the column,

#### minyaloth

##### New Member

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

##### MrExcel MVP, Moderator
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?

Replies
8
Views
166
Replies
20
Views
421
Replies
3
Views
93
Replies
6
Views
84
Replies
4
Views
527

1,127,326
Messages
5,624,018
Members
416,006
Latest member
PCaffrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back