Nesting Problems
 

Like many before me, I find that when I create logical arguments seperately
they work fine, when nesting them together there is a problem. It's usually a
hit and miss affair where the logic behaves correctlyin some situations, but
gives incorrect reults other time. Any help for the following two problems
would be appreciated!

The Logic:
1.IF(I3=H3,"NO CHANGE",IF(OR(I3="RED",H3="YELLOW"),"IMPROVING"))
2.IF(I4=H4,"NO CHANGE",IF(OR(I4="RED",H4="GREEN,"IMPROVING"))
3.IF(I6=H6,"NO CHANGE",IF(OR(I6="GREEN",H6="GREEN"),"NO CHANGE"))"
4.IF(I7=H7,"NO CHANGE",IF(OR(I7="YELLOW",H7="GREEN"),"IMPROVING") )"
5.IF(I8=H8,"NO CHANGE",IF(OR(I8="YELLOW",H8="RED"),"GETTING WORSE"))"
6.IF(I9=H9,"NO CHANGE",IF(OR(I9="GREEN",H9="RED"),"GETTING WORSE"))"
7.IF(I11=H11,"NO CHANGE",IF(OR(I11="GREEN",H11="YELLOW"),"GETTING WORSE"))"

This Month (H) Last Month (I) Correct Result: Nested
Result:
YELLOW RED 1.IMPROVING 1.
IMPROVING
GREEN RED 2.IMPROVING 2.
IMPROVING
GREEN GREEN 3.NO CHANGE 3. GETTING WORSE
GREEN YELLOW 4.IMPROVING 4. IMPROVING
RED YELLOW 5.GETTING WORSE 5.
IMPROVING
RED GREEN 6.GETTING WORSE 6. IMPROVING
YELLOW GREEN 7.GETTING WORSE 7. GETTING WORSE


PPD PSOD/PCR Variance Nested Result

63 74 -17% (Correct) -100%
74 63 15% (Correct) -100%
0 0 100% (Correct) -100%
0 63 -100% (Correct) -100%

Nesting Problems
 

not really a programming question

=IF(
OR(
AND(A2="YELLOW",B2="RED"),
AND(A2="GREEN",B2<"GREEN")
),"Getting Better",
IF(
OR(
AND(A2="YELLOW",B2="GREEN"),
AND(A2="RED",B2<"RED")
),"Getting Worse",
"No Change")
)



"Patricia" wrote:

Like many before me, I find that when I create logical arguments seperately
they work fine, when nesting them together there is a problem. It's usually a
hit and miss affair where the logic behaves correctlyin some situations, but
gives incorrect reults other time. Any help for the following two problems
would be appreciated!

The Logic:
1.IF(I3=H3,"NO CHANGE",IF(OR(I3="RED",H3="YELLOW"),"IMPROVING"))
2.IF(I4=H4,"NO CHANGE",IF(OR(I4="RED",H4="GREEN,"IMPROVING"))
3.IF(I6=H6,"NO CHANGE",IF(OR(I6="GREEN",H6="GREEN"),"NO CHANGE"))"
4.IF(I7=H7,"NO CHANGE",IF(OR(I7="YELLOW",H7="GREEN"),"IMPROVING") )"
5.IF(I8=H8,"NO CHANGE",IF(OR(I8="YELLOW",H8="RED"),"GETTING WORSE"))"
6.IF(I9=H9,"NO CHANGE",IF(OR(I9="GREEN",H9="RED"),"GETTING WORSE"))"
7.IF(I11=H11,"NO CHANGE",IF(OR(I11="GREEN",H11="YELLOW"),"GETTING WORSE"))"

This Month (H) Last Month (I) Correct Result: Nested
Result:
YELLOW RED 1.IMPROVING 1.
IMPROVING
GREEN RED 2.IMPROVING 2.
IMPROVING
GREEN GREEN 3.NO CHANGE 3. GETTING WORSE
GREEN YELLOW 4.IMPROVING 4. IMPROVING
RED YELLOW 5.GETTING WORSE 5.
IMPROVING
RED GREEN 6.GETTING WORSE 6. IMPROVING
YELLOW GREEN 7.GETTING WORSE 7. GETTING WORSE


PPD PSOD/PCR Variance Nested Result

63 74 -17% (Correct) -100%
74 63 15% (Correct) -100%
0 0 100% (Correct) -100%
0 63 -100% (Correct) -100%

Nesting Problems
 

Hi,
A UDF version of Patrick,s solution

=GetStatus(I3,H3)



Function GetStatus(r1, r2)
Dim Status As String, R As String, G As String, Y As String
Dim P1 As String, P2 As String

R = "RED"
G = "GREEN"
Y = "YELLOW"
P1 = UCase(r1.Value) ' Previous State
P2 = UCase(r2.Value) ' Current State
Status = "NO CHANGE"
With Application
If .Or(.And(P1 = R, P2 = Y), .And(P1 < G, P2 = G)) Then
Status = "IMPROVING"
Else
If .Or(.And(P1 = G, P2 = Y), .And(P1 < R, P2 = R)) Then
Status = "GETTING WORSE"
End If
End If
End With
GetStatus = Status
End Function

"Patricia" wrote:

Like many before me, I find that when I create logical arguments seperately
they work fine, when nesting them together there is a problem. It's usually a
hit and miss affair where the logic behaves correctlyin some situations, but
gives incorrect reults other time. Any help for the following two problems
would be appreciated!

The Logic:
1.IF(I3=H3,"NO CHANGE",IF(OR(I3="RED",H3="YELLOW"),"IMPROVING"))
2.IF(I4=H4,"NO CHANGE",IF(OR(I4="RED",H4="GREEN,"IMPROVING"))
3.IF(I6=H6,"NO CHANGE",IF(OR(I6="GREEN",H6="GREEN"),"NO CHANGE"))"
4.IF(I7=H7,"NO CHANGE",IF(OR(I7="YELLOW",H7="GREEN"),"IMPROVING") )"
5.IF(I8=H8,"NO CHANGE",IF(OR(I8="YELLOW",H8="RED"),"GETTING WORSE"))"
6.IF(I9=H9,"NO CHANGE",IF(OR(I9="GREEN",H9="RED"),"GETTING WORSE"))"
7.IF(I11=H11,"NO CHANGE",IF(OR(I11="GREEN",H11="YELLOW"),"GETTING WORSE"))"

This Month (H) Last Month (I) Correct Result: Nested
Result:
YELLOW RED 1.IMPROVING 1.
IMPROVING
GREEN RED 2.IMPROVING 2.
IMPROVING
GREEN GREEN 3.NO CHANGE 3. GETTING WORSE
GREEN YELLOW 4.IMPROVING 4. IMPROVING
RED YELLOW 5.GETTING WORSE 5.
IMPROVING
RED GREEN 6.GETTING WORSE 6. IMPROVING
YELLOW GREEN 7.GETTING WORSE 7. GETTING WORSE


PPD PSOD/PCR Variance Nested Result

63 74 -17% (Correct) -100%
74 63 15% (Correct) -100%
0 0 100% (Correct) -100%
0 63 -100% (Correct) -100%