Rank within different segments

alizok

Board Regular
Joined
Sep 12, 2002
Messages
88
Office Version
  1. 365
Can someone help me on Ranking formula within different segments. for exaple i have 6 different segments that is in column A and than different numbers in column B i need to rank these numbers by segment. The segment are not sorted so it could be segment 1 in column A1 with number 32 in column B and then segment 5 in A2 with different number. Is there a way for me to rank nubers in colum B without sorting it by segment? Any suggestion is a great help.
thank you very much
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi,

You can try this formula that has to be entered with Ctrl + Shift + Enter
Book1.xls
ABCD
1SegmentNumberRank
2A14
3A23
4B33
5A42
6C51
7D61
8B72
9B81
10A91
Sheet4
 
Upvote 0
thank you very much. This is the formula that i was looking for. :pray: :biggrin:
 
Upvote 0
This formula works when i need to rank in descending order what about ascending? I try changing Large to samall but it didn't work. I also try changing the last Zero to 1 but i still don't get right output. Any suggestions would be great. :rolleyes:
 
Upvote 0
Like this?

=MATCH(B2,SMALL(IF($A$2:$A$100=A2,$B$2:$B$100,""),ROW(INDIRECT("1:100"))),0)

Entered with Ctrl + shift + enter in E2 and dragged down.
Book1
ABCDE
1SegmentNumberRankOpositerank
2A141
3A232
4B331
5A423
6C511
7D611
8B722
9B813
10A914
Sheet4
 
Upvote 0
Be specific.

What does not work, what result do you get?

Did you remember Ctrl + Shift + Enter ?
 
Upvote 0
sorry about it I get this #NAME? and i did enter it with ctr+shift+enter :rolleyes:
 
Upvote 0
i tried re-typing the entire formula again and now I'm getting #N/A for some output and for others i'm getting number. I'm totally confused? What am i doing wrong? why would it have an output in cell e3 but not in e4?
 
Upvote 0
I can't replicate your error.

Try applying the formula to a layout exactly as I posted and then change it to your needs successively. Or post a sample of your data, using the html maker, showing your data and the errors you get.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top