Page 1 of 1
[Solved] Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 1:06 pm
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?
Re: Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 1:30 pm
by Villeroy
=MATCH(LARGE($D100:$AN100;2);$D100:$AN100;0)
Re: Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 1:46 pm
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
Re: Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 2:14 pm
by Villeroy
This is where things become very complicated. No, at this moment I don't have the time to elaborate a viable solution.
Re: Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 2:18 pm
by robtorq
Thank you so much Villeroy - very helpful
Re: [Solved]Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 2:46 pm
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.
Re: [Solved]Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 4:07 pm
by robtorq
Perfect - Thank you
Re: [Solved]Establishing COLUMN when using LARGE function
Posted: Wed Sep 02, 2015 4:19 pm
by Villeroy
And this is a quick&dirty demo how it can be used to solve this problem.