Forum Discussion

Greg Bonaparte's avatar
Greg Bonaparte
Iron Contributor
Feb 09, 2019
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...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 09, 2019

    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.

Greg Bonaparte's avatar
Greg Bonaparte
Iron Contributor
Feb 09, 2019
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?
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 09, 2019

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.

  • Greg Bonaparte's avatar
    Greg Bonaparte
    Iron Contributor
    Feb 11, 2019
    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
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 11, 2019

      Hi Greg,

       

      Sorry, I missed. Do you need the entire formula returns FALSE if ANY of D21, F21, H21 is negative? Or only all combinations of $AB30<$AC30, $AD30<$AE30, $AF30<$AG30 are ignored if any of D21, F21, H21 is negative?

       

      Current formula excludes $AB30<$AC30 if D21 is negative; $AD30<$AE30 if F21 negative and the same for the third combination.

       

      How the formula works you may check if stay on the cell with formula, on ribbon click Formulas->Evaluate formula, it shows step by step all calculations.

      • Greg Bonaparte's avatar
        Greg Bonaparte
        Iron Contributor
        Feb 11, 2019

        D21, F21, H21 should remove only individual "$AB30<$AC30, $AD30<$AE30, $AF30<$AG30". However if all "D21, F21, H21" are negative, the whole formula should be false. 

    • Greg Bonaparte's avatar
      Greg Bonaparte
      Iron Contributor
      Feb 11, 2019

      Follow up: Looking at the results I think you solved the problem but looking at the formula I cant see how logically it is solved.  So I'm questioning my own eyes. Please confirm.

Resources