Re: [libreoffice-users] Re: Calc: Has anyone tested the backward-compatibility of LibreOffice 3.4?

On 7 Jun 2011 at 11:47, Stephan Zietsman wrote:

Date sent:              Tue, 7 Jun 2011 11:47:25 +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

> plino wrote:
> > Even with the array working it's not detecting non-numeric cells properly.
> > Can you upload your spreadsheet somewhere so I can see what I'm doing wrong?
>
> Hi plino,
> sure, the upload link is:
> http://www.2shared.com/file/WuzUYsyS/FindNonNumeric.html
>
> See the formula in cell C2.  Let me know whether or not you succeed.

Interesting...
I did some other things with it.
Calculate number of non-numeric values
=COUNTA($A$2:$A$21)-COUNT($A$2:$A$21)

Interestingly, the 3rd non-numeric value was a text formated filed 
in cell a9.

Was interested if it could be modified for multi-column searches.
Did a same test using H2:I5 and used this formula.

= SMALL((IF(ISNUMBER($H$2:$I$5), "ALL 
GOOD",COLUMN($H$2:$I$5)*1000+ROW($H$2:$I$5))), 1)

That returns the number 8004 and 2nd one returns 9005.
Then used this formula to convert to a cell address.
=CHAR(INT(L2/1000)+64)&MOD(L2,1000)

That only works for first 26 columns, and upto 999 rows, but could 
be modified. 




> Regards
> Jack
>
> -- 
> 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        10842777.379512   |   EINSTEIN     5986464.870851
ROSETTA      3221339.084814   |   ABC          6190575.950334


-- 
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