Re: [libreoffice-users] How to return cell address/reference instead of function result with 2d operations?

-----Original Message-----
From: "Michael D. Setzer II" <mikes@kuentos.guam.net>
To: Brian Barker <b.m.barker@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.barker@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+unsubscribe@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