Comparing two sets of numbers

**FoxyDread** · 03-31-2013, 07:02 AM

I have two columns of 100 generated figures.

I want every number in Column A to be compared against every number in column B (therefore there will be 100*100 pairings) i´d like to know how many times Column A´s value is higher than the value of Column B, is there a function or formula that will compute this without creating a grid.

Thanks in advance.

**benishiryo** · 03-31-2013, 07:55 AM

hi FoxyDread. try:
=SUMPRODUCT(--($A$1:$A$100>$B$1:$B$100))

**martindwilson** · 03-31-2013, 08:00 AM

@ benishiryo that will only compare cell to cell a1 to b1 a2 to b2 ..
op says 100*100 so thats comparing a1-b1,a1-b2,a1-b3 up to a100-b99, a100-b100

**benishiryo** · 03-31-2013, 08:41 AM

@martin:
so is that directed at me? was just wondering because of your signature.

i suppose this array formula might help then:
=SUM(--($A$1:$A$100>TRANSPOSE($B$1:$B$100)))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.