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 <[email protected]>
To: [email protected]
Send reply to: [email protected]
> 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 [email protected]
> In case of problems unsubscribing, write to [email protected]
> 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:[email protected]
mailto:[email protected]
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 [email protected]
In case of problems unsubscribing, write to [email protected]
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
