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 <sziets@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+help@libreoffice.org In case of problems unsubscribing, write to postmaster@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: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 SETI 10852824.042363 | EINSTEIN 5992474.160851 ROSETTA 3225956.279477 | ABC 6208653.014792 -- Unsubscribe instructions: E-mail to users+help@libreoffice.org In case of problems unsubscribing, write to postmaster@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