Rank Function Help

[ashwin28]

Hi,

I have a Salesperson worksheet having the following structure:
Column A Column B
Person1 Sales
Person2 Sales
Person3 Sales
Sub Total
Person 4 Sales
Person 5 Sales
Person 6 Sales
Sub Total

and so forth. When using the RANK Function, I want to exclude the subtotal column in the range field else RANK Function will rank that also. How do I go about excluding the subtotal column in RANK functions range field ?

Thanks for replying

ash

 

[Aladin Akyurek]

Hi,

I have a Salesperson worksheet having the following structure:
Column A Column B
Person1 Sales
Person2 Sales
Person3 Sales
Sub Total
Person 4 Sales
Person 5 Sales
Person 6 Sales
Sub Total

and so forth. When using the RANK Function, I want to exclude the subtotal column in the range field else RANK Function will rank that also. How do I go about excluding the subtotal column in RANK functions range field ?

Thanks for replying

ash

How did you calculate subtotals - by means of a formula or otherwise?

 

[ashwin28]

I used the SUM function to arrive at SubTotals

 

[barry houdini]

If your subtotals are in column B what text will be in column A for these rows, or is column A blank?

 

[Aladin Akyurek]

I used the SUM function to arrive at SubTotals

Book13
	A	B	C	D
1	Sales person	Sales	Rank
2	Person1	763.65	3
3	Person2	785.44	2
4	Person3	570.01	5
5	Subtotal	2119.1
6	Person4	618.93	4
7	Person5	418.48	6
8	Person6	931.83	1
9	Subtotal	1969.24
10
Sheet1

C2, copied down:

=IF(A2<>"",IF(A2="Subtotal","",RANK(B2,$B$2:$B$9)-COUNTIF($A$2:$A$9,"Subtotal")),"")

 

[barry houdini]

Aladin,

wouldn't this give incorrect results if any of the individual sales figures are higher than any of the subtotals?

assuming the text "subtotal" in column A when a subtotal appears in column B I'd suggest

=IF(AND(A2<>"",A2<>"subtotal"),SUMPRODUCT(--($A$2:$A$9<>"subtotal"),--($B$2:$B$9>B2))+1,"")

 

[Aladin Akyurek]

Aladin,

wouldn't this give incorrect results if any of the individual sales figures are higher than any of the subtotals?

assuming the text "subtotal" in column A when a subtotal appears in column B I'd suggest

=IF(AND(A2<>"",A2<>"subtotal"),SUMPRODUCT(--($A$2:$A$9<>"subtotal"),--($B$2:$B$9>B2))+1,"")

I failed to test for that possibility. I should have kept it to the method of ranking twice...

Book13
	A	B	C	D
1	Sales person	Sales	I-Rank	F-Rank
2	Person1	763.65	4	3
3	Person2	785.44	5	2
4	Person3	570.01	2	5
5	Subtotal	2119.1
6	Person4	618.93	3	4
7	Person5	418.48	1	6
8	Person6	931.83	6	1
9	Subtotal	1969.24
10
Sheet1

C2, copied down:

=IF(A2="subtotal","",RANK(B2,$B$2:$B$9,1))

D2, copied down:

=IF(N(C2),RANK(C2,$C$2:$C$9),"")

 

[ashwin28]

I am sorry guys I could not understand the last post on I rank and F ranks ? Could you please explain ?

Thanks,

Ash

 

[joelnichols]

The first column is used to make sure there are no "persons" with a higher sales than one of the subtotals I think. Then the next column ranks the persons after that is filtered out.

 

[Aladin Akyurek]

I am sorry guys I could not understand the last post on I rank and F ranks ? Could you please explain ?

Thanks,

Ash

I-Rank (intermediate ranking) eliminates ranking unwanted values, that is, the subtotals that you create with a SUM formula. F-Rank (final ranking) is the desired ranking, based on I-Rank.