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

Hi :)  
Working out the first day temperatures plummeted below zero here was the only day this year that i 
got a chance to sleep outdoors.  I completely missed all of the long hot summer due to moving.  I 
got a bit of a surprise when i poked my head out of ny bivvie and saw the landscape covered in 
frost!
Regards from 
Tom :)





> ________________________________
> From: Kolbjørn Stuestøl <kolbjoern@stuestoel.no>
> To: users@global.libreoffice.org 
> Sent: Wednesday, 23 January 2013, 15:16
> Subject: 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
-- 
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