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.



> 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


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