Determine Largeness of a Value Within Range

[Roybzer]

I really thought that this was going to be simple, but I just can't figure it out.

I have a list of 70 values in a range.

There are multiple rows of values for each of these columns. For this example, the rows could be a physical good, and the rows are customers. The values in the table represent the number of each item each customer has.

I want to be able to establish by checking each item, what relative position of largeness that it holds within the range. I can't sort the table, as this will change for each customer, so is there a way of looking at a value and saying that it is the nth largest cell within the given range?

Using the large(array, n) function requires knowing n(the nth largest value). I'm looking for a way of calculating n.

Thanks

 

[Toadstool]

Hi RoyZber,

I'm not sure I'm following correctly but do you mean the RANK?

Here you specify the Customer number and Item number and the rank of that cell is expressed as the rank with the Items and the Customer.

RoyBzer.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7		Cust	Item	Cust Rank	Item Rank
2	Cust 1	48	32	94	33	50	19	58		3	3	2	4
3	Cust 2	37	11	71	70	55	93	84
4	Cust 3	55	73	80	56	33	39	82
5	Cust 4	44	46	30	45	19	34	33
6	Cust 5	18	63	17	95	25	96	70
7	Cust 6	78	20	100	92	45	46	17
8	Cust 7	25	79	96	77	59	93	53
9	Cust 8	53	49	64	15	37	62	34
Sheet1

Cell Formulas
Range	Formula
B1:H1	B1	="Item "&COLUMN()-1
L2	L2	=RANK.EQ(INDEX($B$2:$H$9,$J2,$K2),OFFSET($A$1,$J2,1,,COLUMNS($B$1:$H$1)))
M2	M2	=RANK.EQ(INDEX($B$2:$H$9,$J2,$K2),OFFSET($A$1,1,$K$2,ROWS($A$2:$A$9)))
A2:A9	A2	="Cust "&ROW()-1

 

[Roybzer]

Hi RoyZber,

I'm not sure I'm following correctly but do you mean the RANK?

Here you specify the Customer number and Item number and the rank of that cell is expressed as the rank with the Items and the Customer.

RoyBzer.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7		Cust	Item	Cust Rank	Item Rank
2	Cust 1	48	32	94	33	50	19	58		3	3	2	4
3	Cust 2	37	11	71	70	55	93	84
4	Cust 3	55	73	80	56	33	39	82
5	Cust 4	44	46	30	45	19	34	33
6	Cust 5	18	63	17	95	25	96	70
7	Cust 6	78	20	100	92	45	46	17
8	Cust 7	25	79	96	77	59	93	53
9	Cust 8	53	49	64	15	37	62	34
Sheet1

Cell Formulas
Range	Formula
B1:H1	B1	="Item "&COLUMN()-1
L2	L2	=RANK.EQ(INDEX($B$2:$H$9,$J2,$K2),OFFSET($A$1,$J2,1,,COLUMNS($B$1:$H$1)))
M2	M2	=RANK.EQ(INDEX($B$2:$H$9,$J2,$K2),OFFSET($A$1,1,$K$2,ROWS($A$2:$A$9)))
A2:A9	A2	="Cust "&ROW()-1

Hi Toadstool,

Yes, that's exactly what I'm looking for.

I knew there had to be a simple solution.

I had been working on a ">=" countif workaround, but this is definitely more elegant.


Thanks