how to find ranking value of one column grouped on another column

[zerocred]

I have the following sample sheet
I want to identify the item ranked by fail value for each test subgroup?
The test groups are not the same size.

test item fail
1 1 10.3
1 3 10.1
2 1 100
2 2 99
2 3 101
2 4 100
3 1 112
3 2 111
3 3 109

ideal output:

test item fail rank
1 1 10.3 2
1 3 10.1 1
2 1 100 2
2 2 99 1
2 3 101 3
2 4 100 2
3 1 112 4
3 2 111 3
3 3 109 1

I don't want to have to sort the columns - there are 1000's of rows and it
wouldn't be able to identify the worst failure case with a =if(rank=1.. test.

Currently I do it by exporting the table to Access and easily find the
min/max etc of a group - but then I need to re-export back to Excel to do
more calculations. The copy/pasting takes several minutes!

Suggestions comments welcome!
Z

 

[zerocred]

I forgot to mention the test column is not contiguous. it may be 1,2,3, 77,
88 etc.

 

[Mike H]

Hi,

I'm not sure I follow the desired results in your example


3 1 112 4 (3)
3 2 111 3 (2)
3 3 109 1 (1)

Shouldn't the desired result be as in the brackets?

Try this

=SUMPRODUCT(--(C1<($A$1:$A$9=A1)*$C$1:$C$9))+1

Or for inverse rank


=SUMPRODUCT(--($A$1:$A$9=$A1),--($C1>$C$1:$C$9))+1

Mike

 

[zerocred]

That's it! Thanks a lot.
I have to think how it works - but it is exactly what I want!

 

[zerocred]

Thats it! - Thanks!

I see now I screwed up my example - your numbers in brackets are right, mine
are wrong.

I have to figure out how sumproduct does it...
Cheers!

 

[zerocred]

For clarification for other interested parties
ideal output should have read

test item fail rank
1 1 10.3 2
1 3 10.1 1
2 1 100 2
2 2 99 1
2 3 101 3
2 4 100 2
3 1 112 3
3 2 111 2
3 3 109 1
*****
Thanks to Mike H for his quick reply.

 

[Mike H]

Hi,

I'm glad that worked and thanks for the feedback. If you want to figure out
how it works by yourself then don't read on but if you want help then this is
how it works


=SUMPRODUCT(--(C1<($A$1:$A$9=A1)*$C$1:$C$9))+1

This bit
$A$1:$A$9=A1 tells the formula what to include in each calculation
and returns the array so only the first 2 elements will count when
evaluating A1
{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

In Excel TRUE and FALSE as boolean values can be considered to be 1 or 0 and
in fact in the formula are forced to 1 or 0 by the double unary but that's
another subject. So, this array is multipled by the numbers in column C. As
only the first 2 elements are counting we'll ignore the rest and get

TRUE;TRUE * 10.3;10.1 = 10.3;10.1

Each number greater than A1 returns 1 and the rest return zero so we get an
array of zeroes which are added up to make zero to which one is added to make
C1 rank as 1

The processs is then repeated for A2 producing the array

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

This is added up and =1 to which 1 is added which makes C2 rank as 2

Probably not the clearest explanation in the world but I hope it helps.

Mike

 

[Rasoul Khoshravan]

Hi,

I'm glad that worked and thanks for the feedback. If you want to figure out
how it works by yourself then don't read on but if you want help then this is
how it works

=SUMPRODUCT(--(C1<($A$1:$A$9=A1)*$C$1:$C$9))+1

This bit
$A$1:$A$9=A1 tells the formula what to include in each calculation
and returns the array so only the first 2 elements will count when
evaluating A1
{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

In Excel TRUE and FALSE as boolean values can be considered to be 1 or 0 and
in fact in the formula are forced to 1 or 0 by the double unary but that's
another subject. So, this array is multipled by the numbers in column C. As
only the first 2 elements are counting we'll ignore the rest and get

TRUE;TRUE * 10.3;10.1 = 10.3;10.1

Each number greater than A1 returns 1 and the rest return zero so we get an
array of zeroes which are added up to make zero to which one is added to make
C1 rank as 1

The processs is then repeated for A2 producing the array

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})

This is added up and =1  to which 1 is added which makes C2 rank as 2

Probably not the clearest explanation in the world but I hope it helps.

Mike

Very nice solution and nice explanation.
I think sumproduct is the most powerful function in excel and there is
no end to its usage. Everyday I see a new application for it.
Initially sumproduct is depicting internal vector multiplication in
algebra.
Now many years away from school I can understand the importance of
algebra and the role it has in solving problems.
In the mean time as a pair for internal (or dot) multiplication of
vectors we also have another one called external vector
multiplication. I am wondering if there is an Excel equivalent for it.

 

[zerocred]

For those interested:
The ranking was taking minutes because it was scanning through some 30000
records for matches.

Since my groups are never larger than 15 and each group is contiguous in col
A, I made the sumproduct range a relative index +/- 16 -more than twice the
size of my groups and put a few (17) blank lines in at the top starting the
records from row 17.

The thing ranks the whole 30k records in a couple of secs.

Here is the revised version.

=SUMPRODUCT(--($A1:$A32=$A17),--($B17>$B1:$B32))+1