Rank within different segments

[alizok]

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

 

[alizok]

Unfortunately I don't know how can I paste image on this board but what happens is that the formula with "small" doesn't work for all the numbers for some reason it doesn't work for rank 1 and 2 instead I get "#N/A" therefore all the rank are off by 2. Please see below simple paste. Your help is greatly appreciated

                  correct		
segmetnt	share	Rank	large	small
1	        62.80    3      3	     1
1	       68.20	    5      1	     3
1	       61.85	    2      4	   #N/A
1	       61.40	    1      5	   #N/A
1	       65.18	    4      2	    2
2	       57.30	    2      4	   #N/A
2	       61.05     3      3	     1
2	       56.57	    1      5	   #N/A
2	       67.94	    4      2	     2
2	       74.22	    5      1       3
3	       65.92     6      1	     4
3	       55.77	    2      5	   #N/A
3	       58.57	    4      3	     2
3	       63.89	    5      2	     3
3	       55.86	    3      4	     1
3	       54.30	    1      6	   #N/A

 

[Aladin Akyurek]

Unfortunately I don't know how can I paste image on this board but what happens is that the formula with "small" doesn't work for all the numbers for some reason it doesn't work for rank 1 and 2 instead I get "#N/A" therefore all the rank are off by 2. Please see below simple paste. Your help is greatly appreciated

                  correct		
segmetnt	share	Rank	large	small
1	        62.80    3      3	     1
1	       68.20	    5      1	     3
1	       61.85	    2      4	   #N/A
1	       61.40	    1      5	   #N/A
1	       65.18	    4      2	    2
2	       57.30	    2      4	   #N/A
2	       61.05     3      3	     1
2	       56.57	    1      5	   #N/A
2	       67.94	    4      2	     2
2	       74.22	    5      1       3
3	       65.92     6      1	     4
3	       55.77	    2      5	   #N/A
3	       58.57	    4      3	     2
3	       63.89	    5      2	     3
3	       55.86	    3      4	     1
3	       54.30	    1      6	   #N/A

Is your data really sorted "segment"?

 

[alizok]

no the data is not sorted by the segment. I just sorted it here so, that you can see the problem

 

[Aladin Akyurek]

no the data is not sorted by the segment. I just sorted it here so, that you can see the problem

OK. Is it possible to sort your data on the Segment column? If the data is large, you could opt for sorting and settle for a fast working formula system.

By the way the formula Fairwinds suggested should work as advertised:

Book6
	A	B	C	D	E
1	segmetnt	share	Rank	large	small
2	1	62.8	3	3	3
3	1	68.2	5	1	5
4	1	61.85	2	4	2
5	1	61.4	1	5	1
6	1	65.18	4	2	4
7	2	57.3	2	4	2
8	2	61.05	3	3	3
9	2	56.57	1	5	1
10	2	67.94	4	2	4
11	2	74.22	5	1	5
12	3	65.92	6	1	6
13	3	55.77	2	5	2
14	3	58.57	4	3	4
15	3	63.89	5	2	5
16	3	55.86	3	4	3
17	3	54.3	1	6	1
Sheet1

D2:

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

E2:

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

These formulas must be confirmed with control+shift+enter instead of just enter.

 

[alizok]

I still have problem the samll formula doesn't work no matter how i do it sorte the segment or not I still get #N/A when it comes to rank 1 and 2 in each segment. I don't have a problem when it comes to Large formula it works both ways sorted segment and non sorted.

 

[fairwinds]

Post the formula as you have it in your sheet and describe exactly in wich cells your data is.

 

[alizok]

={MATCH(B13,SMALL(IF($A$3:$A$279=A13,$B$3:$B$279," "),ROW(INDIRECT("3:279"))),0)} entered with ctrl+shift+enter

In column "A" i have different segments (not sorted) in column "B" i have points that need to be rank and in column "C" is where I'm entering the above mentioned formula.

 

[fairwinds]

The ROW(INDIRECT("3:279")) part should always start with 1, change to

ROW(INDIRECT("1:277"))

 

[alizok]

Thank you very much i guess that was the problem.

would you please explain the function of row(indirect("1:100"))?

 

[fairwinds]

Glad we sorted it

It is a way of creating an array of 1 to 279 {1,2,3,4 etc...

Giving the {1,2,3,4... smallest values.