Nested function

[Guest]

I am trying to create a formula where when each of three consitions is met
with a specific response/text string.
Spreadsheet sample
Col. G Col. J Col. N
Col. O
Target Target Target
II-A
Good Standing Good Standing Good Standing
Good Standing
Good Standing Target Target
Target
Target Good Standing Target
II-A

Situation: If a system receives a "Target" indicator in column G and a
"Target" indicator in either one or both cols. J and N, they should receive a
new indicator "II-A" in column O. If a system receives "Good Standing" in
columns G, J, and N, they receive "Good Standing" as their final indicator in
col O. If they receive a "Good Standing" in Col G, but a "Target" indicator
in either or both cols. J and N, they should recieve a "Target" indicator in
Col. O. I used the following formula, but it isn't working:
=IF((AND(LEFT(G4,3)="tar",LEFT(N4,3)="tar"), "II-A
Improvement"),IF(AND(LEFT(G4,3)="tar",LEFT(J4,3)="tar"),"II-A
Improvement"),IF(AND(LEFT(G4,3)="goo",LEFT(J4,3)="goo",LEFT(N4,3)="goo","Good
Standing","Target"))). Please help! Thanks!

 

[Biff]

Hi!

Try this:

=IF(AND(G4="target",OR(J4="target",N4="target")),"II-A",IF(AND(G4="good
standing",J4="good standing",N4="good standing"),"good
standing",IF(AND(G4="good
standing",OR(J4="target",N4="target")),"target","")))

Biff

 

[Guest]

Thank you so much! I did have to include one other "scenario" to get the
formula to work. I'm enclosing the corrected formula below. The One you
sent did two "groups" correctly, but displayed nothing for the "Target"
groups. Thanks again for your quick reply! BBS

=IF(AND(G4="target",OR(J4="target",N4="target")),"II-A
Improvement",IF(AND(G4="good standing",J4="good standing",N4="good
standing"),"Good Standing",IF(AND(G4="good
standing",OR(J4="target",N4="target")),"Target",IF(AND(G4="Target",OR(J4="Good Standing",N4="Good Standing")),"Target","Target"))))