Rank Function Help

ashwin28

New Member
Joined
Dec 18, 2005
Messages
11
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
:rolleyes:
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
ashwin28 said:
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
:rolleyes:

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If your subtotals are in column B what text will be in column A for these rows, or is column A blank?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

ashwin28 said:
I used the SUM function to arrive at SubTotals
Book13
ABCD
1Sales personSalesRank
2Person1763.653
3Person2785.442
4Person3570.015
5Subtotal2119.1 
6Person4618.934
7Person5418.486
8Person6931.831
9Subtotal1969.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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

barry houdini said:
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
ABCD
1Sales personSalesI-RankF-Rank
2Person1763.6543
3Person2785.4452
4Person3570.0125
5Subtotal2119.1  
6Person4618.9334
7Person5418.4816
8Person6931.8361
9Subtotal1969.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

New Member
Joined
Dec 18, 2005
Messages
11
I am sorry guys I could not understand the last post on I rank and F ranks ? Could you please explain ?

Thanks,

Ash
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
ashwin28 said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,089
Members
412,310
Latest member
mark884
Top