• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with formula

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.

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"
 

Attachments

  • Test.xlsx
    9.5 KB · Views: 3
I'm not sure what you mean by 'higher rank'; a rank which has a larger number or a more senior (lower number).
Anyway, try this formula which can be tweaked by changing some of the <,>, to other things such as >=, <=.
In D2, copied down:
Code:
=IF(C2=0,"OFF",IF(SUMPRODUCT((A2>$A$2:$A$21)*(C2>$C$2:$C$21)*($C$2:$C$21>0))>0,"BAD",""))
The table does NOT need to be sorted for this to work.
 
If just one higher ranking (more senior, lower number in column A) has just 1 shift that could lead to many 'BAD's, so to highlight those you could have another column in row 2 with:
Code:
=IF(C2=0,"",IF(SUMPRODUCT((A2<$A$2:$A$21)*(C2<$C$2:$C$21)*($C$2:$C$21>0)),"???",""))
to show who they might be.
 
Back
Top