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

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.
Thank you.
I was hoping there was an easier way doing it.

My first suggestion was =MATCH(A1:A31<0,A1:A31,0) but this does not works.
Instead I used the array formula {=IF(A1:A31<0;1;0)} in B1. Quite 
similar to your =A1<0. (In LibreOffice TRUE=1 and FALSE=0).
The next step is the same as you suggests: =N(MATCH(1;B1:B31;0))
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.

I am running version LibreOffice 4.0 in Norwegian on Windows 7 if that 
matters.

Perhaps some programmers will add a FIND.FIRST.OCCURRENCE(n<0,A1:A31) 
function in the future? :-)

> You may choose to hide column B, of course - or you could put its 
> values away somewhere else on the sheet or even on a different sheet.
>
> Incidentally, you are unlikely to get temperatures "below 0 degrees 
> (metric)", as the SI temperature scale is kelvin, and its zero is the 
> absolute zero of temperature!  But yes: I appreciate that you mean the 
> celsius scale.  ;^)
Yes, you are right. :-) I am using Celcius, not Kelvin of course.
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