- 543K Members
- 4,173 Online
- 647K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Perplexed!! Why does this not work? "OR" criteria added to a working formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
## Perplexed!! Why does this not work? "OR" criteria added to a working formula

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: Perplexed!! Why does this not work? "OR" criteria added to a working formula

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019 05:19 AM

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,"")

Labels:

22 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019 05:33 AM

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019 05:42 AM

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, "")

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019 05:54 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019 06:11 AM

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?

with ($AB30<$AC30)*($D$21>=0). Do I replace parentheses with 'quote' symbol? Can you give example?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019 06:24 AM

Yes

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-09-2019 06:48 AM

SolutionSorry, 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.

Best Response confirmed by
Greg Bonaparte (Frequent Contributor)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2019 03:30 PM

This worked perfectly. Thank you!!!!!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2019 05:53 PM

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

=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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2019 05:59 PM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 12:25 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 07:18 AM

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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2019 02:28 PM

Greg, that 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, (($D21>0)+($F21>0)+($H21>0)) ), $Q30/$P30,"")

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019 02:24 AM

Hi Thank you for your assistance. I could not use your code here because It produced an odd result. Too much too explain Just now. Id rather spend the time explaining more carefully what I'm aiming for:

First please note the change I made in your code. This below is our template, Not the original from the beginning of this conversation.

=IF(AND(

IF($D21>0,$AB30<$AC30,1),

IF($F21>0,$AD30<$AE30,1),

IF($H21>0,$AF30<$AG30,1),

$AH30<$AI30,

$AJ30<$AK30,

$AL30<$AM30,

$AN30<$AO30,

$AP30<$AQ30,

(($D21>0)+($F21>0)+($H21>0))

), $Q30/$P30,"")

(The change is the elimination of J21 requirement)

So here is what I'm shooting for:

As long as d21 or f21 or h21 are greater than zero, then q30/p30 computes.

The key word is "OR" here.

However if d21 and f21 and h21 are negative, then q30/p30 should not compute.

Hopefully this clarifies. Thanks

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2019 07:13 AM

Hi Greg,

What formula do

(($D21>0)+($F21>0)+($H21>0)) is the equivalent of OR(($D21>0),($F21>0),($H21>0))

they return the same result. IF all of above cells are negative that OR returns FALSE. Entire AND(...,FALSE) also returns FALSE and IF gives empty text as result.

If any of above is positive, when OR returns TRUE, AND(...,TRUE) returns what previous conditions give and entire IF gives Q30/P30 if TRUE, otherwise empty string.

As for the latest - if, as you say, in case of any of D21, F21, H21 is positive the formula shall always return Q30/P30 it's not clear why do we need other conditions under AND. Now we check if any of above is positive and after that apply other conditions.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2019 03:24 AM

Sergei, I figured out the problem. I have not been explaining my manual process correctly.

If I see that $D21>0 I manually add $AB30<$AC30, to $AH30<$AI30, $AJ30<$AK30, $AL30<$AM30,$AN30<$AO30, $AP30<$AQ30), $Q30/$P30,"")

If I see that $F21>0 I manually add $AD30<$AE30, to $AH30<$AI30, $AJ30<$AK30, $AL30<$AM30,$AN30<$AO30, $AP30<$AQ30), $Q30/$P30,"")

If I see that $H21>0 I manually add $AF30<$AG30, to $AH30<$AI30, $AJ30<$AK30, $AL30<$AM30,$AN30<$AO30, $AP30<$AQ30), $Q30/$P30,"")

I do the opposite If d21 or F21 OR h21 become less than zero by manually removing those added codes.

However if at any time all 3 become negative the result of Q30/P30 will not compute because at least one of the 3 D21,F21,H21 are in the code preventing Q30/P30 from being true.

Can you make a code out of this manual process?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2019 01:57 PM

Hi Greg,

Could you please give me an example when formula doesn't work, perhaps I don't understand something. From my point of view it works as you described.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2019 03:30 PM

In my manual code a macro sorts the results of $Q30/$P30, correctly leaving all blank fields at the bottom of the sort. In your automated code the blanks are randomly anywhere in the sort.

My manual code:

=IF(AND(

$AB30<$AC30,

$AD30<$AE30,

$AF30<$AG30,

$AH30<$AI30,

$AJ30<$AK30,

$AL30<$AM30,

$AN30<$AO30,

$AP30<$AQ30),

$Q30/$P30,"")

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2019 04:34 PM

We didn't speak about the sorting, we spoke about the formula for the concrete set of cells. If you apply that formula to the next row, i.e. to check $Q31/$P31, criteria also will be like $AB31<$AC31 and on the top we check $L22, etc (not $L21). Correct?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2019 04:47 PM

everything you said is correct except L22. The D21, F21 and H21 are static positions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2019 04:57 PM

Ah, I just checked your code. I see where you are going with your sort question. D21 f21 and h21 should be $D$21 and so on. Wow I'm surprised I didn't see that. Great job Sergei!!!

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2019 05:13 PM

The final corrected code below works perfectly. Thank you sir!!!

=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,

(($D$21>0)+($F$21>0)+($H$21>0))

), $Q30/$P30,"")

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-15-2019 06:16 AM

Hi Greg - so far so good, you are welcome

Related Conversations

Outlook Message box GUI blacked out when sending attachment through Excel

midohioboarder
in
Office 365
on
01-24-2020
226
Views

0 Likes

0 Replies

A problem with the Zoom level of a Tab

Tavory
in
Discussions
on
11-07-2019
357
Views

0 Likes

9 Replies

New version of Cortana [released today] is suggesting to use Work/School account instead of Personal

HotCakeX
in
Discussions
on
10-25-2019
333
Views

0 Likes

8 Replies

Pivot Table StDev calculates different value then the STDEV formula

zsoltturkosi
in
Excel
on
10-21-2019
199
Views

0 Likes

4 Replies

PWA fullscreen like IE11 kiosk mode

rogihee
in
Discussions
on
10-19-2019
430
Views

0 Likes

5 Replies

Trying to add multiple users to distribution group and getting error: Cannot validate argument

Test SharePoint
in
Office 365
on
10-12-2019
845
Views

0 Likes

4 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft