[Solved] Establishing COLUMN when using LARGE function

Not sure where to post? Post here!

[Solved] Establishing COLUMN when using LARGE function

Postby robtorq » Wed Sep 02, 2015 1:06 pm

Please can someone help me -

I have a Calc Spreadsheet where I use rows of unordered numbers (72 columns per row). at the end of each row, I use the LARGE functions to establish the 4th 3rd 2nd and 1st
largest numbers in that row eg: =LARGE($D100:$AN100;2) (=the 2nd largest number in the row). I need to know which Column the number it found is in - is this possibe?
Last edited by robtorq on Wed Sep 02, 2015 2:22 pm, edited 1 time in total.
OpenOffice 4.1.1 on Windows 7
robtorq
 
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

Re: Establishing COLUMN when using LARGE function

Postby Villeroy » Wed Sep 02, 2015 1:30 pm

=MATCH(LARGE($D100:$AN100;2);$D100:$AN100;0)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25446
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Establishing COLUMN when using LARGE function

Postby robtorq » Wed Sep 02, 2015 1:46 pm

Thank you Villeroy - it works with one exception - if for example, there are equal high numbers (say 2 lots of 90), the Column for both is returned as the same. Any ideas
OpenOffice 4.1.1 on Windows 7
robtorq
 
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

Re: Establishing COLUMN when using LARGE function

Postby Villeroy » Wed Sep 02, 2015 2:14 pm

This is where things become very complicated. No, at this moment I don't have the time to elaborate a viable solution.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25446
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Establishing COLUMN when using LARGE function

Postby robtorq » Wed Sep 02, 2015 2:18 pm

Thank you so much Villeroy - very helpful
OpenOffice 4.1.1 on Windows 7
robtorq
 
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

Re: [Solved]Establishing COLUMN when using LARGE function

Postby Villeroy » Wed Sep 02, 2015 2:46 pm

There is an OFFSET function which can be used to match something within a range X columns offset to the original range where X is the matched column number of the previous operation.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25446
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved]Establishing COLUMN when using LARGE function

Postby robtorq » Wed Sep 02, 2015 4:07 pm

Perfect - Thank you
OpenOffice 4.1.1 on Windows 7
robtorq
 
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

Re: [Solved]Establishing COLUMN when using LARGE function

Postby Villeroy » Wed Sep 02, 2015 4:19 pm

And this is a quick&dirty demo how it can be used to solve this problem.
Attachments
multi_match.ods
(29.07 KiB) Downloaded 122 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25446
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Beginners

Who is online

Users browsing this forum: No registered users and 12 guests