Percentrank with Index, Match

[briordan]

Hi, I'm trying to use Index, Match with Percentrank but I'm not getting it right. In this sample data, I would like to use Percentrank for "Jan" or "Feb", etc.

[TABLE="width: 276"]
<tbody>[TR]
[TD]State[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD="align: right"]44.29[/TD]
[TD="align: right"]48.04[/TD]
[TD="align: right"]55.47[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD="align: right"]42.27[/TD]
[TD="align: right"]46.24[/TD]
[TD="align: right"]51.03[/TD]
[/TR]
[TR]
[TD]Arkansas[/TD]
[TD="align: right"]38.48[/TD]
[TD="align: right"]43.76[/TD]
[TD="align: right"]51.96[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD="align: right"]45.14[/TD]
[TD="align: right"]48.51[/TD]
[TD="align: right"]51.76[/TD]
[/TR]
[TR]
[TD]Colorado[/TD]
[TD="align: right"]23.71[/TD]
[TD="align: right"]28.34[/TD]
[TD="align: right"]35.57[/TD]
[/TR]
[TR]
[TD]Connecticut[/TD]
[TD="align: right"]25.96[/TD]
[TD="align: right"]28.43[/TD]
[TD="align: right"]36.94[/TD]
[/TR]
[TR]
[TD]Delaware[/TD]
[TD="align: right"]33.95[/TD]
[TD="align: right"]35.93[/TD]
[TD="align: right"]44.04[/TD]
[/TR]
[TR]
[TD]Florida[/TD]
[TD="align: right"]58.09[/TD]
[TD="align: right"]59.99[/TD]
[TD="align: right"]64.9[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[TD="align: right"]45.77[/TD]
[TD="align: right"]49.22[/TD]
[TD="align: right"]56.29[/TD]
[/TR]
[TR]
[TD]Idaho[/TD]
[TD="align: right"]23.6[/TD]
[TD="align: right"]28.36[/TD]
[TD="align: right"]35.87[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What would be your formula if you had just the Jan range?

 

[briordan]

Just for Jan, it would be [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=PERCENTRANK(B2:B11, B2)[/TD]
[/TR]
</tbody>[/TABLE]
I should have explained, I'm trying to rank vertically (by state).

 

[Aladin Akyurek]

Just for Jan, it would be [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]=PERCENTRANK(B2:B11, B2)[/TD]
[/TR]
</tbody>[/TABLE]
I should have explained, I'm trying to rank vertically (by state).

Wouldn't copying across then down just do what you want?

 

[briordan]

Wouldn't copying across then down just do what you want?

Sure. But I need the flexibility to, for example, compare "Mar" and "Nov" side-by-side. My actual dataset is quite large, so I would like to take advantage of Index, Match.

 

[Aladin Akyurek]

Sure. But I need the flexibility to, for example, compare "Mar" and "Nov" side-by-side. My actual dataset is quite large, so I would like to take advantage of Index, Match.

A1:D11 houses the data, the headers included.

F1: Feb

F2, copied down:

=PERCENTRANK(INDEX($B$2:$D$11,0,MATCH(F$1,$B$1:$D$1,0)),
  INDEX($B$2:$D$11,ROWS(F$2:F3),MATCH(F$1,$B$1:$D$1,0)))

 

[briordan]

A1:D11 houses the data, the headers included.

F1: Feb

F2, copied down:

=PERCENTRANK(INDEX($B$2:$D$11,0,MATCH(F$1,$B$1:$D$1,0)),
  INDEX($B$2:$D$11,ROWS(F$2:F3),MATCH(F$1,$B$1:$D$1,0)))

Thank you so much, AA. I had been struggling with this. A small note: it's ...ROWS(F$2:F2)..., of course.

Thanks again.

 

[Aladin Akyurek]

Thank you so much, AA. I had been struggling with this. A small note: it's ...ROWS(F$2:F2)..., of course.

Thanks again.

You are welcome. Yes, it's ROWS($F$2:F2)...