Ignoring blank cells in OR formula

[MGIRC]

In Excel 2013, I have a formula similar to this:

=IF(OR(C2>B2, C2>A2, B2>A2),1)

I want to return 1 if any of the inequalities is true. But if one of the cells in my OR statement is blank, I don't want the formula to treat the blank as zero and evaluate but instead read false and move on to the next inequality.

I have searched quite a bit through IFBLANK and IFNUMBER but still not getting this right--help would be very much appreciated. Thanks.

 

[RCBricker]

In Excel 2013, I have a formula similar to this:

=IF(OR(C2>B2, C2>A2, B2>A2),1)

I want to return 1 if any of the inequalities is true. But if one of the cells in my OR statement is blank, I don't want the formula to treat the blank as zero and evaluate but instead read false and move on to the next inequality.

I have searched quite a bit through IFBLANK and IFNUMBER but still not getting this right--help would be very much appreciated. Thanks.

=IF(OR(C2="",B2="",A2=""),"False",IF(OR(C2>B2,C2>A2,B2>A2),1))

 

[MGIRC]

=IF(OR(C2="",B2="",A2=""),"False",IF(OR(C2>B2,C2>A2,B2>A2),1))

Thank you, but this doesn't quite resolve it--I don't want to return the whole if statement as false if just one of my cells is blank. In that case, I am looking for the individual equality to return false and continue evaluating the others.

For example: if C2=5, B2=blank, A2=1, I would want to return true (C2>A2 is true).

And if I had C2=5, B2=blank, A2=blank, I want to return false (none of the inequalities is true). Is that possible?

 

[Eric W]

That's a surprisingly tricky question! I tried quite a few variations that all had at least 1 unsuccessful test case. This one I think works:

<max(a2:c2)),(b2<><max(b2:c2))),1)
=IF(OR((A2 <> "")*(A2 < MAX(A2:C2)),(B2 <> "")*(B2 < MAX(B2:C2))),1)

It's a bit ugly, and doesn't scale well at all. I'll keep playing with it, but let me know if that's what you're looking for.</max(b2:c2))),1)
</max(a2:c2)),(b2<>

 

[jtakw]

Hi,

I think this "Old School" formula might work:

=IF(OR(AND(C2<>"",B2<>"",C2>B2),AND(C2<>"",A2<>"",C2>A2),AND(B2<>"",A2<>"",B2>A2)),1)

 

[Jonmo1]

I think if you can give a better 'worded' description of what the formula intended to do, you might get more responses.
I mean, forget about Trues and Falses, this one greater than that one etc..
Just a plain english description of what it's suppposed to do.

Are you trying to verify the numbers are shown in descending order left to right?

 

[RCBricker]

Hi,

I think this "Old School" formula might work:

=IF(OR(AND(C2<>"",B2<>"",C2>B2),AND(C2<>"",A2<>"",C2>A2),AND(B2<>"",A2<>"",B2>A2)),1)

I was just going to post this exact formula

 

[MGIRC]

Thanks for the suggestions all. It seemed surprisingly complicated to me as well... I do think that this works: =IF(OR(AND(C2<>"",B2<>"",C2>B2),AND(C2<>"",A2<>"",C2>A2),AND(B2<>"",A2<>"",B2>A2)),1)

But does get awkward as it scales. For some more background on this problem, this is a social work context. We are analyzing assessment scores over time (initial, 3-month, 6-month, 12-month intervals) and looking at score increases, score decreases, etc. Because not every individual manages to be present for each assessment, the data set has blanks. I think I was hoping for some simple "do not treat blanks as zero" function to allow flexibility in different formulas, but it seems there may not be a quick fix.

 

[Eric W]

Here's the "neatest" version I came up with. It requires a helper row.

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=MAX(A2:$C2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=IF(SUMPRODUCT(--(A2:C2<>""),--(A2:C2<a1:c1< font="">)</a1:c1<>),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in A1, then copy it to B1:C1. Then the formula is in D1. That scales nicely, but requires the helper row. I've tried to incorporate the helper row into the formula, but haven't managed yet. Also, if you use different formulas based on this, I can't say whether you can even use this technique, it's not very generalized. If you have multiple people on different rows, then the helper rows can add up quickly.

I'm still poking around, but I don't know that I'll improve on this. Let me know if you have follow-up questions about this.

 

[MGIRC]

Really interesting! Thanks for this solution, it's definitely beyond what I would be able to produce. I'll continue to work with my data set to see what options work best. Appreciate all the suggestions.