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?
[Solved] Establishing COLUMN when using LARGE function
[Solved] Establishing COLUMN when using LARGE function
Last edited by robtorq on Wed Sep 02, 2015 2:22 pm, edited 1 time in total.
OpenOffice 4.1.1 on Windows 7
Re: Establishing COLUMN when using LARGE function
=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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Establishing COLUMN when using LARGE function
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
Re: Establishing COLUMN when using LARGE function
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Establishing COLUMN when using LARGE function
Thank you so much Villeroy - very helpful
OpenOffice 4.1.1 on Windows 7
Re: [Solved]Establishing COLUMN when using LARGE function
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved]Establishing COLUMN when using LARGE function
Perfect - Thank you
OpenOffice 4.1.1 on Windows 7
Re: [Solved]Establishing COLUMN when using LARGE function
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice