[Solved] Establishing COLUMN when using LARGE function

Discuss the spreadsheet application
Post Reply
robtorq
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

[Solved] Establishing COLUMN when using LARGE function

Post by robtorq »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Establishing COLUMN when using LARGE function

Post by Villeroy »

=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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
robtorq
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

Re: Establishing COLUMN when using LARGE function

Post by robtorq »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Establishing COLUMN when using LARGE function

Post by Villeroy »

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
robtorq
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

Re: Establishing COLUMN when using LARGE function

Post by robtorq »

Thank you so much Villeroy - very helpful
OpenOffice 4.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved]Establishing COLUMN when using LARGE function

Post by Villeroy »

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
robtorq
Posts: 6
Joined: Wed Sep 02, 2015 12:51 pm

Re: [Solved]Establishing COLUMN when using LARGE function

Post by robtorq »

Perfect - Thank you
OpenOffice 4.1.1 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved]Establishing COLUMN when using LARGE function

Post by Villeroy »

And this is a quick&dirty demo how it can be used to solve this problem.
Attachments
multi_match.ods
(29.07 KiB) Downloaded 297 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply