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 <[email protected]>
WWW: http://pietvanoostrum.com/
PGP key: [8DAE142BE17999C4]
--
To unsubscribe e-mail to: [email protected]
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
