Excel Formulas - Many OR Statements

[nikolasm]

Hello,

I would like to write the following in a more compact form:

=IF(OR(A1>B1,A2>B2,A3>B3,...,A99>B99,A100>B100),1,0)

Basically, I want to check row by row if Ax is greater than Bx and, if true for any row (or rows), return 1 else 0.

This would also help me answer another thread I have, so two birds with one stone!

Thanks,
Nik

[daddylonglegs]

Try this formula

=IF(SUMPRODUCT((A1:A100>B1:B100)+0)>0,1,0)

[Jason Eric]

Try this formula

=IF(SUMPRODUCT((A1:A100>B1:B100)+0)>0,1,0)

This one works should work perfectly......

[bebo021999]

Daddylonglegs' should be shorter a bit:
=--(SUMPRODUCT(--(A1:A100>B1:B100))>0)
Or CSE:
=--OR(A1:A100>B1:B100)

[nikolasm]

Haha, thank you! I have no clue why that works, but that's what I wanted! If you don't mind me asking, why is the "+0" necessary? And what does it mean to sum the array A1:A100>B1:B100?

[bebo021999]

A1:A100>B1:B100 = {A1>B1;A2>B2;...;A100>B100} ={TRUE;FALSE;TRUE;...}
With TRUE~1, FALSE~0
--(A1:A100>B1:B100)
or
(A1:A100>B1:B100)*1
or
(A1:A100>B1:B100)+0
will convert text to number
={1;0;1;...}
SUMPRODUCT will sum all items to get the total.
If SUMPRODUCT(...)>0, that means there is at least 1 to be counted.

[Vicky Singh]

Hi Nik,

The desired output can be achieved using formula =IF(A1>B1,1,0) at column C and drag.

Regards,

VS

[Jason Eric]

The desired output can be achieved using formula =IF(A1>B1,1,0) at column C and drag.

Hi Vicky!
It won't work... He wants only one cell to give him a True or Fasle if any of the A's is greater than it's corresponding B's...

Have fun,
Jason

[BB1972]

DUPLICATE THREAD

Duplicate here: http://www.excelforum.com/excel-form...31#post3154431

nikolasm, please refrain, per the Forum rules, from duplicating posts - this can result in forum members wasting the time they are donating to help others, as the query may have been answered in the duplicate thread.