IF Formula Help Please

[Joe Gieder]

Fist, thank you for your help.
I have this array formula in Z2:
{=IF(AND((AG2)="S",(AH2)="S"),"Y",IF(OR((AG2)="U",(AH2)="U"),"R",IF(AG2:AN2="S","G","R")))}
but it doesn't work. When it evals the AND and OR portions it works ok but
when S is in AG2:AN2 it doesn't give the result of "G", it stays a "Y". What
did I do wrong? Also do I have the AND and OR functions written correctly?

Thanks in advance for your help
Joe

 

[Sandy Mann]

Try:

=IF(AND((AG2)="S",(AH2)="S"),"Y",IF(OR((AG2)="U",(AH2)="U"),"R",IF(SUM(--(AG2:AN2="S")),"G","R")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[PCLIVE]

I'm assuming you want to know if all eight cells (AG2:AN2) are S.
If that is the case, then the last IF statement needs to be first. The way
you have it, the first IF statement is satisfied before it gets to the last
one.

Try is this way:

=IF(COUNTIF(AG2:AN2,"S")=8,"G",IF(AND(AG2="S",AH2="S"),"Y",IF(OR(AG2="U",AH2="U"),"R")))

HTH,
Paul

 

[T. Valko]

It depends on which set of conditions should have precedence. The way you
have the formula written:

=IF(AND((AG2)="S",(AH2)="S"),"Y",

takes precedence over everything else.

IF(AG2:AN2="S","G",

Does that mean if *every* cell in that range =S?

Try putting this test first:

=IF(AND(AG2:AN2="S"),"G",IF(AND(AG2="S",AH2="S"),"Y",.............)

 

[John C]

Have you actually evaluated your formula?

the portion IF(AG2:AN2="S" is only matching if AG2="S" and discarding the
rest.

Assuming you are saying that assuming your original AND and OR conditions
are not met, then you want to see if "S" appears by itself in any of the
range AG2:AN2, you could use this formula (not array).

=IF(AND((AG2)="S",(AH2)="S"),"Y",IF(OR((AG2)="U",(AH2)="U"),"R",IF(COUNTIF(AG2:AN2,"S")>0,"G","R")))

Hope this helps.