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