Re: [libreoffice-users] Calc - Count a "similar sequence"

cje wrote:

> Is there a way to count a sequence of data as long as they are similar?
> We have a datasheet were the sales of our sales men are imported. 
> One of the information we’re using is counting for how long one sales man
> has been on top.
>
> Ex.:
> Sales man#1
> Sales man#1
> Sales man#1
> Sales man#2
> Sales man#1
> Sales man#3
>
> Is here a way that calc can detect that the data is changing and stop the
> counting? 
> The above sample should return 3
>
> Looking forward to any suggestion.

You can write a macro for that, e.g. in Basic.

Here is one for the above problem:

------------------------------------------------------------------------
Function CountEquals(r())
        ' r must be a one row or one column range
        
        If Lbound(r,1) = Ubound(r,1) Then
                idx = 2
        Else
                idx = 1
        End If
        
        num = 0
        idx1 = Lbound(r,idx)
        idx2 = Lbound(r,3-idx)
        first = r(idx1,idx2)
        While idx1 <= Ubound(r,1) and idx2 <= Ubound(r,2)
                If r(idx1,idx2) = first Then
                        num = num + 1
                End If
                If idx = 1 Then
                        idx1 = idx1 + 1
                Else
                        idx2 = idx2 + 1
                End If
        Wend

        CountEquals = num
        
End Function
------------------------------------------------------------------------

Now you can use in a cell, e.g. =CountEquals(A5:A10)
-- 
Piet van Oostrum <piet@vanoostrum.org>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]


-- 
To unsubscribe e-mail to: users+unsubscribe@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