Comparing data in rows

ebandme

New Member
Joined
Oct 24, 2006
Messages
7
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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

New Member
Joined
Oct 24, 2006
Messages
7
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,959
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top