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.

Brian Barker  


--
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