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

On 27 Nov 2015 at 3:56, Edwar Cifuentes wrote:

Subject:                Re: [libreoffice-users] How to return cell address/reference 
instead
        of function result with 2d operations?
To:                     users@global.libreoffice.org
From:                   Edwar Cifuentes <edwarcifuentes@gmail.com>
Copies to:              TomW <tomwebb@fairpoint.net>
Date sent:              Fri, 27 Nov 2015 03:56:58 -0500

> Hey, this works! I need to analiyse slowly and thoroughly but there's a 
> part I can't get into my head right from the start:
> A1:C2=MAX(A1:C2)  <- what's that equal sign doing there? what does this 
> part mean?

I agree that it works, but I also don't understand exactly how it is working. 
Since it is an array function, it must be going thru the array of cells, but what 
exactly it is doing, I don't know?? 

Did also, change my formula to a single cell formula, but it is very long, and 
doesn't handle correctly if there are two o r more cells with the high value.

=IF(IFNA(MATCH(MAX($A$1:$C$2),A1:A2,0),"")<>"","A","")&IF(IFNA(MATC
H(MAX($A$1:$C$2),B1:B2,0),"")<>"","B","")&IF(IFNA(MATCH(MAX($A$1:$C
$2),C1:C2,0),"")<>"","C","")&MAX(IFNA(MATCH(MAX($A$1:$C$2),A1:A2,0),
0),IFNA(MATCH(MAX($A$1:$C$2),B1:B2,0),0),IFNA(MATCH(MAX($A$1:$C
$2),C1:C2,0),0))

Putting the MAX($A$1:$C$2) in a cell would make it a lot shorter since it is 
used a lot.

I have only since your original message, and then this reply. Did see the 
other solution as well. 


> Thanks Tom
>
> On the archive I saw someone else gave another working solution but I 
> didn't get his replies on my inbox. Why? Are you getting my replies?
>
> On 11/26/2015 09:26 AM, TomW wrote:
> > On 2015-11-25 17:26, Edwar Cifuentes wrote:
> > > Hi.
> > >
> > > 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 this:
> > > 1    2    3
> > > 4    5    6
> > > C2
> > >
> > > Instead of this:
> > > 1    2    3
> > > 4    5    6
> > > 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.
> > >
> > > Thank you
> >
> >
> > Edwar:
> >
> > 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.
> >
> > Hope this works for you,
> >
> > TomW
>
>
> -- 
> 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


+----------------------------------------------------------+
  Michael D. Setzer II -  Computer Science Instructor      
  Guam Community College  Computer Center                  
  mailto:mikes@kuentos.guam.net                            
  mailto:msetzerii@gmail.com
  http://www.guam.net/home/mikes
  Guam - Where America's Day Begins                        
  G4L Disk Imaging Project maintainer 
  http://sourceforge.net/projects/g4l/
+----------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS
ROSETTA     37271340.284882   |   SETI        68395213.026815
ABC         16613838.513356   |   EINSTEIN    79440881.457695


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