Nesting IF/OR Statement

[Wendy]

I have a spread sheet in which cells L2:L6 must be worked in order, and in
cell L7 I want to put a Yes or No if those cells were worked in order. a Y
or an N means I have completed the work for those cells. For ex, if its goes
in this order -
L2 = y, and L3= y, and L4,5,6 are all N, then I should have a YES in L9
because I started with L2 and worked my way down.If I did this,

L2= y, L3= y, L4 = n, L5 =y, and L6 = n, than it shows that I worked my
cells out of order and they should have a big fat NO in box L9. Here is the
IF/OR statement I wrote, but its not working.

=IF((OR(L2="Y",L3="N",L4="N",L5="N",L6="N"),"YES","NO"),IF(OR(L2="Y",L3="Y",L4="N",L5="N",L6="N"),"YES","NO"),IF(OR(L2="Y",L3="Y",L4="Y",L5="N",L6="N"),"YES","NO"),IF(OR(L2="Y",L3="Y",L4="Y",L5="Y",L6="N"),"YES","NO"))

I'm very new with excel, and not sure about where to put comma's, etc. or if
this would even be an IF statement function as opposed to a LOOKUP. Any help
would be very much appreciated. Thanks!

Wendy

 

[Duke Carey]

This is an array formula that you need to enter by pressing Ctrl-Shift-Enter

=MAX(--(L2:L6="y")*ROW(L2:L6))<MAX(--(L2:L6="n")*ROW(L2:L6))

It will return FALSE if you are out of order and TRUE if you are IN order

 

[Raz]

i think your equation will work Wendy, the only thing you need to change is
OR to AND.
replace all OR to AND and this should work.

 

[Raz]

try this

=IF(AND(L2=""Y"",L3=""N"",L4=""N"",L5=""N"",L6=""N""),""YES"",IF(AND(L2=""Y"",L3=""Y"",L4=""N"",L5=""N"",L6=""N""),""YES"",IF(AND(L2=""Y"",L3=""Y"",L4=""Y"",L5=""N"",L6=""N""),""YES"",IF(AND(L2=""Y"",L3=""Y"",L4=""Y"",L5=""Y"",L6=""N""),""YES"",""NO""))
))

 

[Glenn]

Doesn't work. Try her specific example (L2= y, L3= y, L4 = n, L5 =y, and L6 = n).

 

[Wendy]

Hi Duke, Thank you for getting back to me so quickly. I tried your array
formula, and it worked for the first scenario where the cells were worked in
order, but not for the second scenario where I worked L2, L3, started to work
L3 and stopped and then went on to work L4 and part of L5. It should have
returned a false according to your logic. It did return a TRUE for the first
scenario. I did do the contr shift enter. I tried it a couple of ways. I'm
using 2003 excel, does that make a difference?

 

[Wendy]

Thats correct. Maybe I should note too that this will be a repetative task
for a bunch of folks in which I'll have to calculate to make sure they work
their cells in order. So the placement of Y and N in L2:L6 will be
completely different depending on the person that does the work.

 

[Wendy]

Hi Raz, Thank you for getting back to me so quickly. I tried your formula,
but it didnt work and EXCEL didnt offer me an alternative to it. I even did
the contrl+shift+enter and still didnt work out. Is there supposed to be 4
parenthesis at the end?

Thanks,
Wendy

 

[John C]

You could try this:
Insert|Name|Define, variable name will be like Checker, and source would be:
=CONCATENATE($L$2,$L$3,$L$4,$L$5,$L$6)
Then, your formula would be:
=IF(OR(Checker="YYYYY",Checker="YYYYN",Checker="YYYNN",Checker="YYNNN",Checker="YNNNN",Checker="NNNNN"),"Yes","No")

 

[Wendy]

I'm not sure what you mean by this, Insert|Name|Define, variable name will
be like Checker, and source would be:
=CONCATENATE($L$2,$L$3,$L$4,$L$5,$L$6).. Where and how do I do that?

 

[John C]

Your xl menu bar has an option of Insert (with the I underlined), go to menu
Insert, select option Name, which will give you some more options, choose
Define.
This will give a pop-up box of variables (you probably have none at this
time).
In the Names in workbook: field type Checker
In the Refers to: field type the formula (with the = sign).

 

[Wendy]

Ok, I got Checker in the box... When I inserted in the Checker into that
field, you could see it up on my took bar, next to the Formula box, where it
said Checker. But if I put in the formula nothing would happen and the name
Checker would disappear from the formula bar. Also, where do I put the
=concantenate formula as the 'source'?

 

[Wendy]

Hi John, please disregard my last msg... I figured out that what you are
having me do is instead of putting a Y or a N in boxes L2:L6 you are having
me put them all in one field, from left to right instead of on top of each
other. I got to the insert/name/define and I did what you said, but then i
dont see any results. However, if I just put in the name checker into the
'names in workbook': field, I see that name up in the formula tool bar to the
left. I think what you are trying to do is create a user function, right?
It looks cool, I'm just not sure what I'm missing... Please advise..
Wendy~~~

 

[Wendy]

Hi John!!! I got it to work!! WOO HOOOO!!! I didnt quite understand the
last part with the insert define name, but, what I ended up doing was once it
was all concanteonated in a YYYN, or YNYN format, I then just took your
formula and substituted the 'checker' with L8, the location of the filed
those fell in and it works PERFECTLY!!

Thank you Raz, Glen, Duke, and John - I enjoy reading these posts because
I'm learning so much!! Thank you for your help~ God bless you!!

 

[John C]

Glad you got it to work. Essentially, what I was trying to do was create a
variable that wasn't defined in a cell, but you can do that too. To answer
your question, no, that is not a UDF. A UDF would require VBA programming. It
was just defining a variable based on the worksheet, where you don't have to
'waste' a cell to do it. But you can, it works just the same. Anyway, glad
you got it working, and thanks for the feedback