CODEWITHSUNDEEP
arraysexcel
youssouf
youssouf

Reputation: 381

Find if all list1 element are greater than the second list in excel

I have a 2 list that contains numbers.

list1 {a, b, c, d} and list2 {A, B, C, D}.

Is there any way in excel to know if all the values of list1 are greater the same index value in list2. In other word if the AND(a > A, b > B, c> c) is true.

Upvotes: 1

Views: 43

Answers (2)

Forward Ed
Forward Ed

Reputation: 9874

Both Vertical Arrays (Not CSE)

=sumproduct(--(array1>array2))=count(array1)

Both Horizontal Arrays (CSE) 

=sumproduct(--(transpose(array1)>transpose(array2)))=count(array1)

Array 1 Horrizontal Array 2 Vertical (CSE)

=sumproduct(--(transpose(array1)>array2))=count(array1)

Array 2 Horrizontal Array 1 Vertical (CSE)

=sumproduct(--(array1>transpose(array2)))=count(array1)

This is a CSE formula. this means use CONTROL+SHIFT+ENTER instead of just ENTER when you edit or enter your formula.

Upvotes: 2

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

If you are comparing A8:D8 and A9:D9, you can use the following array formula (must press Ctrl+Shift+Enter when entering formula):

= AND((A8:D8-A9:D9)>0)

This returns true if A8>A9, B8>B9, ... etc.

Upvotes: 1

Related Questions