Forum Discussion
Solved
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...
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.
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?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.
- Sergei, I just realized that one issue is not solved. See final formula below:
=IF(AND(
IF($D$21>0,$AB30<$AC30,1),
IF($F$21>0,$AD30<$AE30,1),
IF($H$21>0,$AF30<$AG30,1),
$AH30<$AI30,
$AJ30<$AK30,
$AL30<$AM30,
$AN30<$AO30,
$AP30<$AQ30),
$Q30/$P30,"")
I need the formula to become "not true" if D21 thru H21 is negative. Thank you so much for your assistence