Formula Help for checking order of list and ignoring Zero values

minyaloth

New Member
Joined
Mar 30, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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"
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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)
 

Attachments

  • Capture.PNG
    Capture.PNG
    80.7 KB · Views: 2

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top