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 <kolbjo...@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+h...@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+h...@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
