Nested If Function

[shamrock1118]

SO I have employee schedules sent to me like this:

[TABLE="width: 887"]
<tbody>[TR]
[TD="class: xl66, width: 136, bgcolor: transparent"]Employee Name:[/TD]
[TD="class: xl66, width: 136, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 196, bgcolor: transparent"]name[/TD]
[TD="class: xl67, width: 42, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 84, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Sunday[/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Monday[/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Tuesday[/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Wednesday[/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Thursday[/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Friday[/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Saturday[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Start/End Time:[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]8:30 AM[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"]Week 1[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"]X[/TD]
[TD="class: xl71, bgcolor: transparent"]X[/TD]
[TD="class: xl71, bgcolor: transparent"]X[/TD]
[TD="class: xl71, bgcolor: transparent"]X[/TD]
[TD="class: xl71, bgcolor: transparent"]X[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

And I'm getting them in by the hundreds and I would like to write a formula that reads it for me and tells me,
based on certain criteria, which arbitrary schedule number it is. (Schedule numbers were assigned randomly. They are just being used as a reference.) Each schedule number has a unique start time.

So this is the formula I have going on here for the above schedule:

=IF($C6=BV$4,IF($F6="",IF($G6="X",IF($H6="X",IF($I6="X",IF($J6="X",IF($K6="x",IF($L6="",BV$5,""),""),""),""),""),""),""),"")

[TABLE="width: 513, align: center"]
<tbody>[TR]
[TD="align: center"]BP[/TD]
[TD="align: center"]BQ[/TD]
[TD="align: center"]BR[/TD]
[TD="align: center"]BS[/TD]
[TD="align: center"]BT[/TD]
[TD="align: center"]BU[/TD]
[TD="align: center"]BV[/TD]
[TD="align: center"]BW[/TD]
[TD="align: center"]BX[/TD]
[/TR]
[TR]
[TD="align: center"]7:00 AM[/TD]
[TD="align: center"]7:15 AM[/TD]
[TD="align: center"]7:30 AM[/TD]
[TD="align: center"]7:45 AM[/TD]
[TD="align: center"]8:00 AM[/TD]
[TD="align: center"]8:15 AM[/TD]
[TD="align: center"]8:30 AM[/TD]
[TD="align: center"]8:45 AM[/TD]
[TD="align: center"]9:00 AM[/TD]
[/TR]
[TR]
[TD="align: center"]55[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]57[/TD]
[TD="align: center"]58[/TD]
[TD="align: center"]59[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]63[/TD]
[/TR]
</tbody><colgroup><col><col span="7"><col></colgroup>[/TABLE]


C6 is the column with start time that was sent to me, BV$4 is the column containing the start time that matches the criteria of the schedule number in BV$5.

Now, for SOME UNGODLY REASON my formula works for MOST of the schedules EXCEPT for a select number of schedules. Schedule number 61 for example will not work. I don't understand.

PLEASE HELP.

 

[Special-K99]

I dont understand what youre trying to achieve.

Why have you got all those IFs when the only result would be BV5 or blank?
You could simply MATCH the time against the BP:BX column range.
Further instead of all those IFs you could just change it to

IF(AND(COUNTA(F6:L6)=6,F6=""),BV5,"")

 

[Gerald Higgins]

Special-K99 - I'm equally confused by the requirement, but I don't think it's to check for 6 blanks in the range F6:L6.

I think it includes a check that F6 and L6 are both blank, and G6:K6 are all = "X".

 

[wideboydixon]

I don't think that's quite reflective of the formula. Perhaps:

=IF(AND(COUNTA($F6:$L6)=5,$F6="",$L6="",$C6=BV$4),BV$5,"")

WBD

 

[shamrock1118]

I figured it out actually. The problem with the proposed solutions is that each schedule is unique to a certain days-off to days-on combination. For example, the employee could have Sunday, Monday, and Thursday off and it would calculate as three days off, however; if they have Sunday, Tuesday, and Saturday off, it would still calculate as three days off however the schedules are different and therefore should return a different schedule number.

For future reference, I got the function to work. I think there were just corrupt cells or something because I opened a new workbook and redid the formulas and TA-DA! It worked... strange. Thanks for the help though!

 

[shamrock1118]

OH! Actually I see what you were getting at... that's a really good idea actually.