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