2015 Archives by date, by thread · List index

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

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

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.

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