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.
>
> Brian Barker
>
>
> --
> 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
>
+----------------------------------------------------------+
Michael D. Setzer II - Computer Science Instructor
Guam Community College Computer Center
mailto:mi...@kuentos.guam.net
mailto:msetze...@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+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
