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

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.

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