-----Original Message----- From: "Michael D. Setzer II" <mi...@kuentos.guam.net> To: Brian Barker <b.m.bar...@btinternet.com>, users@global.libreoffice.org Sent: Fri, 27 Nov 2015 4:53 Subject: Re: [libreoffice-users] How to return cell address/reference instead of function result with 2d operations?
On 27 Nov 2015 at 12:31, Brian Barker wrote: Date sent: Fri, 27 Nov 2015 12:31:39 +0000 To: users@global.libreoffice.org From: Brian Barker <b.m.bar...@btinternet.com> Subject: Re: [libreoffice-users] How to return cell address/reference instead of function result with 2d operations? > At 22:07 27/11/2015 +1000, Michael D. Setzer II wrote: > >On 27 Nov 2015 at 11:11, Brian Barker wrote: > >>At 03:56 27/11/2015 -0500, Edwar Cifuentes wrote: > >>>On 11/26/2015 09:26 AM, Tom Webb wrote: > >>>>On 2015-11-25 17:26, Edwar Cifuentes wrote: > >>>>>When using functions like MAX on a 2d range how can I get the > >>>>>cell's address/reference returned instead of the result value? > >>>>>e.g. if I have this table starting at A1... > >>>>>1 2 3 > >>>>>4 5 6 > >>>>>How can I get ... C2 Instead of ... 6 > >>>>> > >>>>>I know I could something containing MATCH if it were just a > >>>>>single row or column but here I have a table spanning several > >>>>>rows and columns and there seems to be no equivalent of MATCH > >>>>>for these situations. > >>>> > >>>>The following will find the address of the Max value. If there > >>>>are duplicate values it shows the last location. > >>>>=ADDRESS(MAX((A1:C2=MAX(A1:C2))*ROW(A1:C2)),MAX((A1:C2=MAX(A1:C2))*COLUMN(A1:C2)),4) > >>>>This is an array function and you need to use Ctrl-Shift-Enter > >>>>instead of Enter. > >>> > >>>Hey, this works! > >> > >>Sadly, I don't think it does. If the largest value happens to be > >>duplicated in different rows and columns, the formula generates the > >>address of neither but instead that of the intersection of that row > >>and that column. Try changing either B1 or A2 to 7: for such > >>separate changes the formula works. But now change both B1 and A2 > >>to 7: the formula generates neither B1 nor A2 but B2 - which is not > >>either of the cells containing the equal largest values. > >>[...] > > > >In my testing, I didn't see that, it appears to result with the last > >one highest values. > > So you cannot have tested with the particular rogue case I > identified, then: identical largest values in both different rows and > different columns? The problem will show up, of course, only if the > intersection of the relevant row and column - the incorrect result - > doesn't happen to be one of the cells containing those identical > largest values. You are correct, I had generally had the highest value in C2, and then duplicated it in other cells, and it kept giving me c2, but when c2 has a lower value but c1 did, it did still give c2. I've only seen that the conditional formatting with the condition to equal max(a1:c2) highlights all the matching cells. Just don't know if there is a further method needed to get the cell address or just highlighting the cell. [My comments follow. -- jl] Conditional formatting significantly slows updates in large spreadsheets. I suggest using MAX(OFFSET(A1,(MAX(A1:C2=MAX(A1:C2))-1),0,1,COLUMNS(A1:C2))*ROW(A1:C2)=MAX(A1:C2))*COLUMN(A1:C2) instead of MAX(A1:C2=MAX(A1:C2))*COLUMN(A1:C2) as the Column parameter in the ADDRESS() function call. This should find the last occurrence of the maximum value in the last row that contains that value. That said, when I tried the originally proposed formula I ended up with an array displaying F6 L6 R6 F12 L12 R12 instead of C3. I don't understand why. -- Jim -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
