Using RANK and SUMIF together?

[flouee]

Hello!

I am looking for a formula to select specific rows in a data sheet (according to a set of criterias), calculate the rank (RANK) for some of these rows (a new set of criterias), and then summarize those ranks. Is this possible and how would I do it? I was thinking about combining RANK with SUMIF but I have not been able to get it to work...

An example; I want to select all rows where column B="5", and then summarize the ranks of rows where B="5" and C="10".

(Each row of data can be in different "rank groups", so it is not possible to add an extra column for each row and calculate the rank directly in the data sheet; the rank will depend on which other rows are included in each set.)

If it helps, I need the ranks for the rows in order to do the Mann-Whitney test.

 

[flouee]

Hm, that gives 23 instead of 22, and for end score 4 it gives 13 instead of 14. That is, the total sum of the ranks is still 36, but it is not divided totally correct?

(http://office.microsoft.com/en-us/excel-help/rank-function-HP010062535.aspx?CTT=5&origin=HP010079190)

 

[Jerry Sullivan]

Hi, I appreciate Barry's helping you reach a solution to this- especially since I can usually only respond to these at night.

I'll bow out so he can work with you, but just one observation...

I'm not clear on what you mean by "calculate the sum of the ranks of each group's Tot IgG mgG-2".

You probably don't mean additive sum (2+3+1+4) = 10 ...that wouldn't be very useful.

It looks like this is what you are looking for after all. If that is the case, then I don't see how the rankings themselves affect the result.
If you have 3 items in the group the sum will be 1+2+3= 6. For 6 items it woulld be 6!=21.
The only difference is whether you are using simple ranking vs unique - then the ties would affect the result 1+2+2+2+5+6= 17.

Let me know if I missed the point, but if that is correct the formula would be greatly simplified.

Good luck!

 

[flouee]

The point I think you are missing is that I do not want to summarize all ranks, only those belonging to the right group. First, we give all mice a rank (regardless of end score-group), then we summarize the ranks of those mice belonging to the end score=0, and separately the ones having end score=4. Thus, the total rank for both groups will be 6!, but the distribution between the two groups is what I need, in order to see if the parameter I investigate is significant different between the groups. Any clearer?

 

[Jerry Sullivan]

Okay, it took me a bit to catch on, but now I understand.

This should work for the adjusted rankings...

Excel Workbook
	A	B	C	D	E	F
1	Mouse	Tot IgG mgG-2	IFN-?	Plack assay	PCR	End score
2	Mouse 2	7290	1	450	845000	0
3	Mouse 1	21870	9	100	216000	0
4	Mouse 3	7290	243	290	2070000	4
5	Mouse 3	65610	6561	10	80000	0
6	Mouse 1	7290	81	170	1500000	4
7	Mouse 2	72900	0,333333333	30	460000	4
8	Mouse 3	2430		40	1090000	0
9	Mouse 1	21870		150	660000	0
10
11		Sum of ranks for end score=	0		22
12		Sum of ranks for end score =	4		14
Sum Ranks with Adjustment

Excel 2007

Cell Formulas
Range	Formula
E11	=SUMPRODUCT(($F$2:$F$9=C11)*((RANK($B$2:$B$9,$B$2:$B$9,1))+(0.5*(COUNT($B$2:$B$9)+1-RANK($B$2:$B$9,$B$2:$B$9,0)-RANK($B$2:$B$9,$B$2:$B$9,1)))))
E12	=SUMPRODUCT(($F$2:$F$9=C12)*((RANK($B$2:$B$9,$B$2:$B$9,1))+(0.5*(COUNT($B$2:$B$9)+1-RANK($B$2:$B$9,$B$2:$B$9,0)-RANK($B$2:$B$9,$B$2:$B$9,1)))))

Since you want to be able to change the criteria for different groups, I'd suggest you reference the groups from C11 and C12 so that you don't have to edit the formulas. You can just swap in the values into the formulas if you prefer.

 

[flouee]

Ah, great! Thanks a lot! It works perfectly!

 

[flouee]

**** it, I just realized I forgot something that I also did not explain properly!

Your formula only work if I include all mice from the data in the rank calculation, but I only want some specific mice to be included. Better example:

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:59px;"><col style="width:94px;"><col style="width:45px;"><col style="width:66px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td></tr><tr style="height:55px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Mouse</td><td>Tot IgG mgG-2</td><td>Group</td><td>End score</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Mouse 2</td><td style="text-align:right; ">7290</td><td style="text-align:right; ">1</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>Mouse 1</td><td style="text-align:right; ">21870</td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Mouse 3</td><td style="text-align:right; ">7290</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Mouse 3</td><td style="text-align:right; ">65610</td><td style="text-align:right; ">1</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Mouse 1</td><td style="text-align:right; ">7290</td><td style="text-align:right; ">2</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>Mouse 2</td><td style="text-align:right; ">72900</td><td style="text-align:right; ">4</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>Mouse 2</td><td style="text-align:right; ">2324</td><td style="text-align:right; ">3
</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>Mouse 3</td><td style="text-align:right; ">3324</td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>Mouse 1</td><td style="text-align:right; ">323</td><td style="text-align:right; ">1</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>Mouse 5</td><td style="text-align:right; ">24323</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td>Mouse 3</td><td style="text-align:right; ">2430</td><td style="text-align:right; ">2</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td>Mouse 1</td><td style="text-align:right; ">21870</td><td style="text-align:right; ">1</td><td style="text-align:right; ">0</td></tr></tbody></table>
I want to select all mice in group 1 and 3, give all of them ranks (related to only these mice, not mice in any other groups), and then summarize only those ranks belonging to the mice in group 1 or 3 with end score = 0.

Basically, I need a quite flexible formula, where I can add additional criteria for those being included in the calculations etc.. I guess that would take me back to the formula suggested by Barry, with some changes for the rank correction factor?

I am sorry for messing it up =/

 

[barry houdini]

The rank formula for the first mouse should be:

=RANK(B2;$B$2:$B$9;1)+(COUNT($B$2:$B$9) + 1 - RANK(B2;$B$2:$B$9;0) - RANK(B2;$B$2:$B$9; 1))/2

Just a quick query, then. I notice that the above formula will rank so that rank 1 is applied to the lowest value....but elsewhere you have shown ranks where the highest value is 1....my suggested formula was on the assumption that the highest value would be 1.......if it's the lowest then you can just switch the > signs for <, i.e.

=SUMPRODUCT(--(F2:F9=0),COUNTIFS(F2:F9,0,B2:B9,"<"&B2:B9)+COUNTIFS(F2:F9,4,B2:B9,"<"&B2:B9)+(COUNTIFS(F2:F9,0,B2:B9,B2:B9) +COUNTIFS(F2:F9,4,B2:B9,B2:B9))/2+0.5)