IF Formula Help Please

J

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
 
S

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
P

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

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",
Click to expand...

takes precedence over everything else.
IF(AG2:AN2="S","G",
Click to expand...

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",.............)
 
J

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.
 
Top