OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Getting text from another cell in the row when using LARGE

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
spitinyari
Newbie
Newbie


Joined: 21 Sep 2011
Posts: 4

PostPosted: Wed Sep 21, 2011 2:11 am    Post subject: Getting text from another cell in the row when using LARGE Reply with quote

I have a set of data showing how old something is and in the next cell I have the thing's name, a bit like this:

5 Dog
17 Cat
84 Bird
23 Dinosaur
45 Car
12 Bike
56 House

I am using a LARGE function on the first column to get the top 3 values, like this:

=LARGE(A$1:A$7;1)
=LARGE(A$1:A$7;2)
=LARGE(A$1:A$7;3)

But I also want to get the corresponding bit of text from column B. Is there a way to do this?

I have used these forums for a while and can normally find what I'm after, but this time I just don't know what to search for, so thought I'd post. Any help would be greatly appreciated!
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Wed Sep 21, 2011 4:25 am    Post subject: Reply with quote

Code:
=LARGE(A$1:A$7;1)&" "&VLOOKUP(LARGE(A$1:A$7;1);$A$1:$B$7;2;0)


Your entries are unique – no two numbers the same?
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)


Last edited by Robert Tucker on Wed Sep 21, 2011 4:41 am; edited 1 time in total
Back to top
View user's profile Send private message
spitinyari
Newbie
Newbie


Joined: 21 Sep 2011
Posts: 4

PostPosted: Wed Sep 21, 2011 4:39 am    Post subject: Reply with quote

You, my friend, are a bone fide spreadsheet genius. That was exactly what I was after, thank you!
Back to top
View user's profile Send private message
spitinyari
Newbie
Newbie


Joined: 21 Sep 2011
Posts: 4

PostPosted: Wed Sep 21, 2011 4:42 am    Post subject: Reply with quote

I do have some numbers the same. I have done a top 5 and the ones that come in at 3 and 4 are both the same number. When getting the description for rank 4, this seems to be causing it to look at the description cell for entry 3 instead. Is there a way round that?

Currently I am seeing something similar to

100 Dog
90 Cat
78 House
78 House

65 Tree

but I should be seeing

100 Dog
90 Cat
78 House
78 Frog

65 Tree
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

PostPosted: Wed Sep 21, 2011 5:12 am    Post subject: Reply with quote

If you have:

5 Dog
17 Cat
84 Bird
84 Dinosaur
45 Car
12 Bike
56 House

in A1:B7 then enter:

Code:
=RANK(A1,$A$1:$A$7,0)+COUNTIF($A$1:A1,A1)-1

in (say) E1 and fill down to E7.

In (say) F1 enter:

Code:
=INDEX(A$1:A$7,MATCH(ROW(),$E$1:$E$100,0))

and fill down to F7.

In (say) G1 enter:

Code:
=INDEX(B$1:B$7,MATCH(ROW(),$E$1:$E$7,0))

and fill down to G7.



Not my genius, I'm afraid, see:

http://www.mrexcel.com/archive/Formulas/7938.html
http://www.cpearson.com/excel/Rank.aspx
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
spitinyari
Newbie
Newbie


Joined: 21 Sep 2011
Posts: 4

PostPosted: Wed Sep 21, 2011 5:38 am    Post subject: Reply with quote

Thanks Robert, that's really helpful
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group