numbering for top 3 highest and lowest value with comment

[Ajinkya]

Dear Frineds,


I were been trying to mark a numbering for top 3 highest and lowest value with loss and gain comment.


Coloumn B having following data

B2= -78%

B3= -75%

B4= -61%

B5= -58%

B6= 43%

B7= 44%

B8= 47%

B9= -16%


Comments are in coloumn C (want a formula)

C2= 1 Loss

C3= 2 Loss

C4= 3 Loss

C6= 3 Gain

C7= 2 Gain

C8= 1 Gain


thanks friends, missing you....

 

[Faseeh]

Hi Ajinkya,


For Largest Value & Comment Respectively First & Second:

[pre]

=INDEX($B$2:$B$9,MATCH(LARGE($B$2:$B$9+ROW($B$2:$B$9)/1000000000,ROW(B1)),$B$2:$B$9+ROW($B$2:$B$9)/1000000000,0),0)

=INDEX($C$2:$C$9,MATCH(LARGE($B$2:$B$9+ROW($B$2:$B$9)/1000000000,ROW(B1)),$B$2:$B$9+ROW($B$2:$B$9)/1000000000,0),0)
For Lowest Value & Comments Respectively:


=INDEX($B$2:$B$9,MATCH(SMALL($B$2:$B$9+ROW($B$2:$B$9)/1000000000,ROW(F1)),$B$2:$B$9+ROW($B$2:$B$9)/1000000000,0),0)

=INDEX($C$2:$C$9,MATCH(SMALL($B$2:$B$9+ROW($B$2:$B$9)/1000000000,ROW(F1)),$B$2:$B$9+ROW($B$2:$B$9)/1000000000,0),0)

[/pre]
press Ctrl+Shift+Enter & Drag down.


Regards,

Faseeh


[ This Marks my 1K posts ]

 

[Ajinkya]

Hi Faseeh,


Congratulations for 1K milestones.


thnaks for ur formula, but it is slightly matching with my requirement, because im looking for "Gain/Loss comment and numbering, should come automatic with formula or something..."

like,

1 Loss

2 Loss

3 Loss

3 Gain

2 Gain

1 Gain

 

[Faseeh]

Thanks U Ajinkya, Try:


=IF(B2=SMALL(DATA,1),"1ST LOSS",IF(B2=SMALL(DATA,2),"2ND LOSS",IF(B2=SMALL(DATA,3),"3RD LOSS",IF(B2=LARGE(DATA,1),"1ST GAIN",IF(B2=LARGE(DATA,2),"2ND GAIN",IF(B2=LARGE(DATA,3),"3RD GAIN",""))))))


Where Data = $B$2:$B$9


Or See this file:


http://dl.dropbox.com/u/60644346/Ajinkya%20Smallest%20%26%20Largest.xlsx


Regards,

 

[Sajan]

Hello,

In the interest of choice, here is one more approach:

=IFERROR(IFERROR(MATCH(TRUE,B2=SMALL(PData,{1,2,3}), 0) & " Loss", MATCH(TRUE,B2=LARGE(PData,{1,2,3}), 0) & " Gain"),"---")


Copy down to additional rows as needed.


Cheers,

Sajan.

 

[Ajinkya]

Thanks Faseeh, its works


Hi Sajan, Tried but nt getting exact output.


Rgds,

Ajinkya

 

[Faseeh]

You are welcome...

 

[Sajan]

Hi Ajinkya,

The formula uses a Name called "PData", which refers to B2:B9 in your original post. (If you prefer, you can replace references to PData with the actual range.)


Using your sample data, I got the following result:

[pre]

1	ColB	ColC
2	-78%	1 Loss
3	-75%	2 Loss
4	-61%	3 Loss
5	-58%	---
6	43%	3 Gain
7	44%	2 Gain
8	47%	1 Gain
9	-16%	---

[/pre]
The formula needs to be put into cell C2, and copied down:

=IFERROR(IFERROR(MATCH(TRUE,B2=SMALL(PData,{1,2,3}), 0) & " Loss", MATCH(TRUE,B2=LARGE(PData,{1,2,3}), 0) & " Gain"),"---")


Hope this helps.


Cheers,

Sajan.

 

[Ajit Chauhan]

Hi Ajinkya,


This is my first post, i hope you will like below solution, if there is any error please suggest:


=IF(IF(B2>0,RANK(B2,$B$2:$B$9),RANK(B2,$B$2:$B$9,1))>3,"",IF(B2>0,RANK(B2,$B$2:$B$9)&" Gain",RANK(B2,$B$2:$B$9,1)&" Loss"))


Use above formula in cell c2 and drag down.

 

[Ajinkya]

Hi Sajan and Ajit,


Thanks, your also tricks are working