Forum Discussion
Perplexed!! Why does this not work? "OR" criteria added to a working formula
I'm not getting an error, I'm just not getting many results. I suspect its because of my greater than or less than zero requirements. The cells that contain the requirements are percentages so 1.0 is greater than zero and 0.1 is less than zero, correct? This formula worked perfectly before I added "OR" & "greater than or less than zero" requirements.
OLD formula that works perfectly:
=IF(AND($AB30<$AC30, $AD30<$AE30, $AF30<$AG30, $AH30<$AI30, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30),$Q30/$P30,"")
NEW formula that works but pulls little data:
=IF(AND( $AB30<$AC30,$D21>0, $AD30<$AE30,$F21>0, $AF30<$AG30,$H21>0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $D21<0, $AD30<$AE30,$F21>0, $AF30<$AG30,$H21>0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $F21<0, $AF30<$AG30,$H21>0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $AD30<$AE30,$F21>0, $H21<0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $AD30<$AE30,$F21>0, $AF30<$AG30,$H21>0, $J21<0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $F21<0, $AF30<$AG30,$H21>0, $J21<0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30,
OR( $F21<0, $H21<0, $AH30<$AI30,$J21>0, $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30
))))))),$Q30/$P30,"")
Sorry, it excludes it if OR condition. To exclude from AND it could be like
=IF(AND( IF($D21>0,$AB30<$AC30,1), IF($F21>0,$AD30<$AE30,1), IF($H21>0,$AF30<$AG30,1), IF($J21>0,$AH30<$AI30,1), $AJ30<$AK30, $AL30<$AM30, $AN30<$AO30, $AP30<$AQ30), $Q30/$P30,"")
If D21 is negative first IF returns TRUE independently of what is in AB30 and AC30 and that doesn't affect other conditions under AND. Please see attached.
22 Replies
Hi Greg,
Depend on what is the logic behind. In your formula OR conditions are nested under AND conditions, like
=IF(AND(cond1,cond2,OR(cond3,cond4)), result, "")
Is that what you'd like to achieve? Or
=IF( OR(AND(cond1, cond2), AND(cond3, cond4)), result, "")
- I want to add more info here: In the old formula I would simply visually look at D21, F21, H21, AND J21. If one of these were negative I would manually remove the formula such as " $AB30<$AC30,". But now, using "OR" Im changing formula from $AB30<$AC30,$D21>0 to $D21< 0
If you'd like to remove $AB30<$AC30 if D21 is negative you may use
($AB30<$AC30)*($D$21>=0)
it gives FALSE if D21 is negative and result of AB30, AC30 comparison otherwise.
- So in every "OR" statement I should replace $AB30<$AC30,$D21>0,
with ($AB30<$AC30)*($D$21>=0). Do I replace parentheses with 'quote' symbol? Can you give example?
