Comparing data in rows

[ebandme]

I have data in the following table:
1 2 3 4 5 6
a 50 53 73 72 62.5 33
b 63 110 68.5 82 30 50.5
c 67 40.5 44 66.5 49 69
d 95.5 95.5 52 124 76.5 43.5
e 65 85 45 86 31 35
f 64 112 75 90 50 15

each column is a score for a particular week for that particular row. What I am trying to do is find a formula or way to calculate how many times row 'a' score was greater than row 'b' score, row 'a' greater than 'c', and so on.
If this needs a sepreate table then fine but my main issue is comparing two rows of data and counting how many times one row is larger than the other.
please help.

 

[erik.van.geit]

Hi, ebandme
Welcome to the Board !!!!!

try something like this
  A    B    C    D    E    F    G  H           I   
1 50   53   73   72   62,5 33                  ROW 
2 63   110  68,5 82   30   50,5                2   
3 67   40,5 44   66,5 49   69      higher than 4   
4 95,5 95,5 52   124  76,5 43,5                    
5 65   85   45   86   31   35      result      3   
6 64   112  75   90   50   15                      

Blad1
[Table-It] version 06 by Erik Van Geit

RANGE FORMULA (1st cell)
I5    =SUMPRODUCT(--(OFFSET($A$1:$F$6,I2-1,,1,)>OFFSET($A$1:$F$6,I3-1,,1,)))

[Table-It] version 06 by Erik Van Geit

TIP: next time you post a table use Table-It (see my signature) or colo's HTMLmaker (see bottom page)

kind regards,
Erik

 

[ebandme]

I appreciate your help but I am still having difficulty.
I am looking for a one number answer that tells me how many times row 1 was greater than row 2.
if there is a formula that does: count how many times row 1's score was greater than row 2's score.

I tried using your example but I received an error - is there a way to use count of sumif formula?

 

[erik.van.geit]

Hi,

it works nicely for me, exactly what you want
so let's find out what your problem might be

you didn't tell in which cells the table is
perhaps you reversed the columnlabels and rownumbers in your example ? therefore I used a table starting in A1

so care to put your table in the same range
fill in a number in I2 and I4
the numbers represent the "relative row" in your table

as you can see in the example I2 has "2" and I3 has "4"
the result is "3": there are indeed 3 values in "row"2 higher than "row"4

I forgot to change the values again to "1" and "2" after testing
so to get the result comparing "row"1 and "row"2, just change I2 and I3 accordingly

best regards,
Erik

 

[ebandme]

thanks - got it.