Changed formula in AJ1 thru AJ15
=IF(AH1<=$AG$1,ADDRESS(MOD(AI1,1000),INT(AI1/1000)),"")
Paste of cells directly from spreadsheet didn't look correctly
13 1 1007 $A$7
2 4011 $D$11
3 8010 $H$10
4 11012 $K$12
5 13007 $M$7
6 17011 $Q$11
7 19015 $S$15
8 24017 $X$17
9 26009 $Z$9
10 29009 $AC$9
11 31015 $AE$15
12 32011 $AF$11
13 32015 $AF$15
14 #VALUE!
15 #VALUE!
On 8 Jun 2011 at 20:19, Michael D. Setzer II wrote:
From: "Michael D. Setzer II"
<mi...@kuentos.guam.net>
To: Stephan Zietsman <szi...@gmail.com>,
users@libreoffice.org
Date sent: Wed, 08 Jun 2011 20:19:23 +1000
Subject: Re: [libreoffice-users] Re: Calc: Has anyone
tested the
backward-compatibility of LibreOffice 3.4?
Priority: normal
Send reply to: users@libreoffice.org
> On 8 Jun 2011 at 10:37, Stephan Zietsman wrote:
>
> Date sent: Wed, 8 Jun 2011 10:37:07 +0200
> Subject: Re: [libreoffice-users] Re: Calc: Has anyone
> tested the
> backward-compatibility of LibreOffice 3.4?
> From: Stephan Zietsman <szi...@gmail.com>
> To: users@libreoffice.org
> Send reply to: users@libreoffice.org
>
> > Stephan wrote:
> > > To get a more user friendly result, use the following formula:
> > >
> > > = IF(MIN(ISNUMBER(G4:H7)), "All values are numeric",
> > > ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))),
> > > MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All
> > > good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), "All good",
> > > COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All good",
> > > ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))))))
> >
> > I just have a small correction to make. For consistency, the first
> > range reference in the formula should also be absolute (i.e. G4:H7
> > should be $G$4:$H$7). So the formula should actually be:
> >
> > = IF(MIN(ISNUMBER($G$4:$H$7)), "All values are numeric",
> > ADDRESS(MIN(IF(ISNUMBER($G$4:$H$7), "All good", ROW($G$4:$H$7))),
> > MIN(IF(ISNUMBER(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All
> > good", ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1)), "All good",
> > COLUMN(OFFSET($G$4:$H$7,MIN(IF(ISNUMBER($G$4:$H$7), "All good",
> > ROW($G$4:$H$7))) - MIN(ROW($G$4:$H$7)), 0, 1))))))
> >
> > > Once again, remember to enter it as an array formula (by pressing
> > > CTRL+SHIFT+ENTER instead of just ENTER).
>
> The Address function was something I didn't recall, use to teach a
> spreadsheet class, but haven't in a long time. Did come up with
> some other testing.
>
> Filled in A1:AF20 with a bunch of numbers, and then I randomly
> put in some "a" characters in cells.
>
> In Cell AG1 placed this to get the number of non-numeric
> =COUNTA(A1:AF20)-COUNT(A1:AF20)
>
> In Cells AH1 thru AH15 put the numbers 1 to 15
>
> In Cell AI1 thru AI15 put this formula
> {= SMALL((IF(ISNUMBER($A$1:$AF$20), "ALL
> GOOD",COLUMN($A$1:$AF$20)*1000+ROW($A$1:$AF$20))),
> AH1)}
>
> Then in AJ1 thru AJ15 put this formula
> =ADDRESS(MOD(AI1,1000),INT(AI1/1000))
>
> This is the result.
> 13 non-numeric fields and their addresses.
>
> 13
> 1
> 1007
> $A$7
>
>
> 2
> 4011
> $D$11
>
>
> 3
> 8010
> $H$10
>
>
> 4
> 11012
> $K$12
>
>
> 5
> 13007
> $M$7
>
>
> 6
> 17011
> $Q$11
>
>
> 7
> 19015
> $S$15
>
>
> 8
> 24017
> $X$17
>
>
> 9
> 26009
> $Z$9
>
>
> 10
> 29009
> $AC$9
>
>
> 11
> 31015
> $AE$15
>
>
> 12
> 32011
> $AF$11
>
>
> 13
> 32015
> $AF$15
>
>
> 14
> #VALUE!
> #VALUE!
>
>
> 15
> #VALUE!
> #VALUE!
>
>
> Could add an if to the AJ formula to only display if less than or
> equal to $AG$1.
>
>
>
> >
> > Regards
> > Stephan
> >
> > --
> > Unsubscribe instructions: E-mail to users+h...@libreoffice.org
> > In case of problems unsubscribing, write to
> > postmas...@documentfoundation.org
> > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> > List archive: http://listarchives.libreoffice.org/www/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
> SETI 10852824.042363 | EINSTEIN 5992474.160851
> ROSETTA 3225956.279477 | ABC 6208653.014792
>
>
> --
> Unsubscribe instructions: E-mail to users+h...@libreoffice.org
> In case of problems unsubscribing, write to postmas...@documentfoundation.org
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/www/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
SETI 10852824.042363 | EINSTEIN 5992474.160851
ROSETTA 3225956.279477 | ABC 6208653.014792
--
Unsubscribe instructions: E-mail to users+h...@libreoffice.org
In case of problems unsubscribing, write to postmas...@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/www/users/
All messages sent to this list will be publicly archived and cannot be deleted
