Adjust PERCENTRANK column reference automatically

[James02]

Hi,

Thank you for reading this.

Question:
PERCENTRANK.INC(D:D,D2,4)

Is there a way to reference the array segment of the formula with by referencing another cell? For example, the formula.. "PERCENTRANK.INC(D:D,D2,4)" referring to column D. I'm wanting to figure out a way for the formula to reference different columns based upon a cell value. So it will switch to column E, F, or G.

I have a fairly mega spreadsheet, and will run into memory limitations and need to limit the amount of formulas, therefore, I hope to minimize the amount of formulas by being able to reference columns I choose. I don't want to have to %rank each column, but just pick and choose the columns I need to % rank overtime.

Does this make sense? Thank you.

https://www.dropbox.com/home?preview=2015-05-21+PERCENTRANK.INC+formula.xlsx

 

[DRSteele]

I think this might help you out a little:

Excel 2012
	A	B	C	D	E
1	use column		b
2	start at row		6
3	finish at row		1048576	last row in Excel
4
5	random #	random #	random #
6	32	59	66		52%
7	42	38	10		33%
8	26	70	23		62%
9	93	83	49		86%
10	10	88	2		90%
11	82	27	38		24%
12	9	28	61		29%
13	55	91	76		100%
14	47	14	44		19%
15	46	82	22		81%
16	47	3	95		5%
17	80	52	7		48%
18	40	2	42		0%
19	13	13	70		14%
20	13	66	100		57%
21	65	44	93		38%
22	58	74	91		67%
23	52	79	64		76%
24	29	74	4		67%
25	85	90	88		95%
26	9	7	61		10%
27	82	48	14		43%
Sheet17

Cell Formulas
Range	Formula
E6	=PERCENTRANK.INC(INDIRECT($C$1&$C$2&":"&$C$1&$C$3),INDIRECT($C$1&ROW(),4))

 

[James02]

Dude! This is freaking EPIC! This is exactly what I was looking for. Legendary! I didn't think anyone would respond either, this is going to be a huge help on my project.

 

[James02]

BTW. this is my final real life formula, it's so complex it's crazy! But it's working like a BAUS:

=PERCENTRANK.INC(IF(A$4:A$2003=A4,INDIRECT($AF$1&$AF$2&":"&$AF$1&$AF$3),""),INDIRECT($AF$1&ROW(),4),4)

 

[DRSteele]

I'm so glad it all worked out for you.