Forum Discussion

KLNY152's avatar
KLNY152
Copper Contributor
Jul 07, 2023

IF AND … unless???

Current formula;

=IF(AND(B17=“Yes”,B18>2.9,B19=“Yes”,OR(B21>19,((B20>24)*(B20<50))),B25>0),”Yes”,”No”)

 

In essence returning a yes/no response to test the suitability of a candidate based on information given. 
however they will be tested against two different pathways. If the longer pathway cannot be done (because time is too short (B18)) then they can do the short even if the above formula returned a ‘no’. 
so I need it to be if the above formula is true, return yes. If the above returns no, but B20>49 AND B18<6 then this formula should return yes. As they will then be suitable because they can do it in place of the longer pathway. 

not sure if this makes any sense. 

please help! 

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 07, 2023

    KLNY152 

    Perhaps

    =IF(
    B18 > 2.9 + N("Long part"),
    IF((B17 = "Yes") * (B19 = "Yes"), "Yes", "No"),
    IF(
        OR(
            B21 > 19,
            (B20 > 24) * (B20 < 50)
        ) * (B25 > 0), "Yes", "No")
)
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 07, 2023
    If I understood your text correctly, this formula should be of some help.
    =IF(AND(B17="Yes", B18>2.9, B19="Yes", OR(B21>19, (B20>24)*(B20<50)), B25>0), "Yes", IF(AND(B20>49, B18<6), "Yes", "No"))

Resources