Re: [libreoffice-users] First occurrence of a negative number in a column

Den 23.01.2013 14:32, skreiv Brian Barker:
> At 13:44 23/01/2013 +0100, Kolbjørn Stuestøl wrote:
> > Den 23.01.2013 07:29, skreiv Brian Barker:
> > > At 22:57 22/01/2013 +0100, Kolbjørn Stuestøl wrote:
> > > > Is there any way to find the first occurrence of a number less (or 
> > > > greater) than a given number in an unsorted array or a column?
> > > > Example: A1:A31 contains a list of temperatures a given month. I 
> > > > would like to find the first day the temperature is below 0 degrees 
> > > > (metric).
> > >
> > > What happens if there is no day in the month when the temperature 
> > > was negative?  Let's say you want the value zero for the resulting 
> > > day number in this case.
> > >
> > > This is a little messy, but it works:
> > >
> > > o In B1, enter =A1<0 and fill it down the column to B31. This 
> > > generates TRUE for your negative values and FALSE otherwise.
> > >
> > > o In your result cell, enter =N(MATCH(TRUE;B1:B31;0)) .  The MATCH() 
> > > function searches for the first TRUE value in the column B values 
> > > and returns the relative position in the array of the first match.  
> > > (Note that this is not necessarily the row number, though will be in 
> > > your example.)  This would return the #N/A error if there is no 
> > > negative temperature; the N() function makes no change to real dates 
> > > but converts this error to zero.
> >
> > Alas the N() function does not works as supposed. It returns "#N/A" 
> > if there are no numbers below zero. I have to figure out a solution.
>
> Oh dear: sorry.  It indeed appears that the behaviour of N() was 
> changed (between versions 3.5. and 3.6) and it no longer works as I'd 
> imagined.  So you'll need something (even messier) such as
>  =IF(ISERROR(MATCH(TRUE;B1:B31;0));0;MATCH(TRUE;B1:B31;0))
> instead.
Thanks. I think this will help me out. Although I would have preferred a 
simpler version :-)

I am new to LibreOffice.
I do have a working program calculating meteorological statistics 
written in a Pascal variant many years ago. But rewriting it in Calc 4.0 
just to learn a bit about this program too.
Kolbjoern
> I trust this helps.
>
> Brian Barker


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted