Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formula Help Please
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formula Help Please
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 Replace @mailinator.com with @tiscali.co.uk "Joe Gieder" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formula Help Please
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 -- "Joe Gieder" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formula Help Please
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",.............) -- Biff Microsoft Excel MVP "Joe Gieder" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Formula Help Please
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. -- John C "Joe Gieder" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|