Aug 11 2022 05:31 PM
Hi
Please may I get some help. I've been trying to get this IF, AND, OR statement to work and have checked that all statements fit within their respective brackets but I think where I've gone wrong may be between each logical step of the OR statement?
=IF($H$6<F11-15,1,IF(OR(IF(AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,E12<0),0,1),(IF(AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,E12<0),0,1)),(IF(AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,E12<0),0,1)),(IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,E12<0),0,1)),(IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,E12<0),0,1)))))
I'm certainly not a good excel equation creator so if there's a better way of doing it I'd love to learn it :)
Thanks!
Aug 12 2022 01:01 AM
Hi @baz
this is really a tricky one, but maybe this could work:
=IF($H$6<F11-15,1,IF(OR(AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,E12<0),
AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,E12<0),
AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,E12<0),
AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,E12<0),
AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,E12<0)),0,1))
Aug 12 2022 01:06 AM
Aug 12 2022 06:06 AM
SolutionThe problem as fixed above is that the 2nd IF statement has a big OR statement as the conditional but no statements for the if true or if false result:
=IF($H$6<F11-15,1,
IF( OR( IF( AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,E12<0),0,1),
(IF(AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,E12<0),0,1)),
(IF(AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,E12<0),0,1)),
(IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,E12<0),0,1)),
(IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,E12<0),0,1))
),
{if true do this},
{if false do this}
)
)
alternatively maybe this would work for you:
=IFS($H$6<F11-15,1,
OR($H$6<=F11,E12>=0),{true},
A12<=0,{true},
B12<=0,{true},
C12<=0,{true},
D12<=0,{true},
TRUE, {false}
)
again I will not assume what value you wanted for the result of that IF() statement being TRUE or FALSE
Aug 12 2022 02:50 PM
Aug 12 2022 03:19 PM
sounds like you found the fix. i have always had trouble with the AND, OR . One hint is to make sure the data you are searching against are numbers. if not you may need to use the value() function.; that will change any alpha into a numeric value.
Aug 12 2022 03:34 PM
Aug 12 2022 03:38 PM - edited Aug 12 2022 03:40 PM
Gidday magerr999
Thanks for that info. Yeah, I discovered I was using the OR function incorrectly. Here's the update I made to it that works:
=IF($H$6<=F11-15,1,IF(OR(AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,G12<0),AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,G12<0),AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,G12<0),AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,G12<0),AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,G12>0))=TRUE,1,0))
but it's blooming long. I think mtarler has come up with the best and shortest version I just need to test it properly and try understand how it works! LMAO
Haven't tried the value() function yet. Do you place the alpha values inside the brackets? And is it a case of A=1, B=2 etc?
Aug 12 2022 06:09 PM
i keep looking at your original formula and I still can't see anything wrong with it, I really don't. I think it's fine. It might have something to do with your data. Or believe it or not, sometimes the formula just won't work and there is nothing wrong with it. I found that if I retyped it in a different cell it will then work fine. good luck.
Aug 13 2022 12:18 AM
Aug 15 2022 09:45 AM
value(a2), value(b2)...... it takes what might be a text and changes it to a numeric value. but what might be easier is to first check your column or row of data using the type command, type(a2), .... it will tell you whether your data is 1 for numeric or 2 for text.
I again looked at your original formula and still don't see anything wrong with it. But I've been wrong before. I would go along with the person 's formula that works for you.
odds are, if you retyped the formula from scratch, it will work for you. imo, bob
Aug 19 2022 09:01 PM
Aug 22 2022 06:53 AM
Aug 24 2022 01:49 PM
Aug 24 2022 02:47 PM
Aug 24 2022 08:11 PM
Aug 12 2022 06:06 AM
SolutionThe problem as fixed above is that the 2nd IF statement has a big OR statement as the conditional but no statements for the if true or if false result:
=IF($H$6<F11-15,1,
IF( OR( IF( AND($H$6>F11,A12<0,B12<0,C12<0,D12<0,E12<0),0,1),
(IF(AND($H$6>F11,A12>0,B12<0,C12<0,D12<0,E12<0),0,1)),
(IF(AND($H$6>F11,A12>0,B12>0,C12<0,D12<0,E12<0),0,1)),
(IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12<0,E12<0),0,1)),
(IF(AND($H$6>F11,A12>0,B12>0,C12>0,D12>0,E12<0),0,1))
),
{if true do this},
{if false do this}
)
)
alternatively maybe this would work for you:
=IFS($H$6<F11-15,1,
OR($H$6<=F11,E12>=0),{true},
A12<=0,{true},
B12<=0,{true},
C12<=0,{true},
D12<=0,{true},
TRUE, {false}
)
again I will not assume what value you wanted for the result of that IF() statement being TRUE or FALSE