OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Max Occurrence + Current "Streak"

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
jmadero
General User
General User


Joined: 02 Jan 2009
Posts: 41

PostPosted: Sat Feb 19, 2011 10:24 am    Post subject: Max Occurrence + Current "Streak" Reply with quote

Hi All,

I'm trying to get two things done in a spreadsheet that I'm working on. I have the following in cells"

Win
Win
Loss
"Blank" (Empty)
Win
Win
Win
Win
Loss
Loss
Loss



What I want to do is
1. Look at "largest streak" for both win and loss, so loss would equal 3 and win would be 4. If I can I want it to skip EMPTY cells, ie. empty does not break the streak


2. Look at current streak (in the above example 3 loss).


I found something similar in excel but it's not working in open office for #1 above

http://www.ozgrid.com/forum/showthread.php?t=84279&page=1

Gives me an error 508. Thanks in advance
Back to top
View user's profile Send private message AIM Address Yahoo Messenger
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Feb 19, 2011 2:34 pm    Post subject: Reply with quote

When your list is in A1:A11 this array formula returns the longest streak of Wins, which is 4...
Code:
MAX(FREQUENCY(IF(A1:A11="Win";ROW(A1:A11));IF(A1:A11<>"Win";ROW(A1:A11))))
and this array formula returns the longest streak of Losses, which is 3...
Code:
MAX(FREQUENCY(IF(A1:A11="Win";ROW(A1:A11));IF(A1:A11<>"Win";ROW(A1:A11))))


Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Feb 19, 2011 6:39 pm    Post subject: Reply with quote

Those formulas don't handle blank cells the way you are wanting.
The attached doc shows one way of dealing with blanks as well as determining the current streak type and current streak count.
I have had to use three helper columns, one for removing the blanks and two for calculating the current streak...
http://www.mediafire.com/view/?098330xql5nd79l
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Sun Oct 14, 2012 3:25 am; edited 1 time in total
Back to top
View user's profile Send private message
jmadero
General User
General User


Joined: 02 Jan 2009
Posts: 41

PostPosted: Sat Feb 19, 2011 10:57 pm    Post subject: Reply with quote

Thank you both. These are both very useful. I'm trying to walk through the more complex one right now, it is quite complex and I'm impressed that you were able to come up with a solution to the blanks issue.
Back to top
View user's profile Send private message AIM Address Yahoo Messenger
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Feb 20, 2011 3:41 am    Post subject: Reply with quote

It can be so much easier with 2 extra columns.
Some sequence of 0 and 1 in column A
B1 =N($A1=0)
B2 =IF($A2=0;B1+1;0) [if this one is 0 add 1 to the preceeding test, else reset to 0)
copy down and get the max from that column

Same with test on 1 in column C
C1 =N($A1=1)
C2 =IF($A2=1;B1+1;0)

With the gaps:
B1 =N(AND(ISNUMBER($A1);$A1=0))
B2 =IF(ISBLANK($A2);B1;IF($A2=1;B1+1;0))

C1 =N($A1=1)
C2 =IF(ISBLANK($A2);C1;IF($A2=0;C1+1;0))

The N() prevents the max/min to be displayed as boolean so you don't have to format the result.

EDIT: fixed error detected by ken
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org


Last edited by Villeroy on Sun Feb 20, 2011 5:44 am; edited 3 times in total
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Feb 20, 2011 5:31 am    Post subject: Reply with quote

Villeroy wrote:
It can be so much easier with 2 extra columns.
Some sequence of 0 and 1 in column A
B1 =N($A1=0)
B2 =IF($A2=0;B1+1;0) [if this one is 0 add 1 to the preceeding test, else reset to 0)
copy down and get the max from that column

Same with test on 1 in column C
C1 =N($A1=1)
B2 =IF($A2=1;B1+1;0)

With the gaps:
B1 =N(AND(ISNUMBER($A1);$A1=0))
B2 =IF(ISBLANK($A2);B1;IF($A2=0;B1+1;0))

C1 =N($A1=1)
C2 =IF(ISBLANK($A2);C1;IF($A2=0;C1+1;0))

The N() prevents the max/min to be displayed as boolean so you don't have to format the result.

After fixing up one tiny little error...
C2 =IF(ISBLANK($A2);C1;IF($A2=0;C1+1;0))
should be...
C2 =IF(ISBLANK($A2);C1;IF($A2=1;C1+1;0))

I've used Villeroy's simpler formulas in Streak Calcs handle blanks v2.ods...
http://www.mediafire.com/view/?jrb92uz6nnb7pyh
Thanks Villeroy.

If your column of Win/Loss/Blank could go further down than row 2001 you will need to edit the addresses in the formulae in C3 and C4...
C3:=IF(INDEX($E$1:$E$2001;SUMPRODUCT(MAX(($A$1:$A$2001<>"")*(ROW($A$1:$A$2001)))))=0;"Win";"Loss")
C4:=MAX(INDEX($E$1:$F$2001;SUMPRODUCT(MAX(($A$1:$A$2001<>"")*(ROW($A$1:$A$2001))));0))
the 2001s will need to be increased.
Using too large a range in SUMPRODUCT can impact on calculation time.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Sun Oct 14, 2012 3:29 am; edited 1 time in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Feb 20, 2011 5:54 am    Post subject: Reply with quote

Thank you, Ken.
Now you introduced another error which makes a difference in F4 where it tests if ISBLANK($D4). But D4 it is not blank. It has a formula.
It should test if ISBLANK($A4) or if $D4=""
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Feb 20, 2011 11:36 am    Post subject: Reply with quote

I should have spotted that.
Changed all the ISBLANKs in E and F to Dn="" then updated the link.
Thanks again Villeroy!

EDIT: Can't leave this thing alone.
Column D with 1s, 0s and blanks is not necessary so I've removed it.
The formula determining the current streak type is now the much simpler...
Code:
=LOOKUP(REPT("Z";255);$A$1:$A$65536)

Now the only cell with the SUMPRODUCT formula currently limiting column A to a maximum of 2000 values is
Code:
=MAX(INDEX($D$1:$E$2001;SUMPRODUCT(MAX(($A$1:$A$2001<>"")*(ROW($A$1:$A$2001))));0))
in C4.
Link updated yet again.
Ken Johnson
Back to top
View user's profile Send private message
jmadero
General User
General User


Joined: 02 Jan 2009
Posts: 41

PostPosted: Tue Feb 22, 2011 11:28 am    Post subject: Reply with quote

Says the link is dead?? I was just about to try to change it in my spreadsheet and take a look at the second version and it says not a valid link...did you take it down? thanks again for all the help to both of you
Back to top
View user's profile Send private message AIM Address Yahoo Messenger
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue Feb 22, 2011 12:09 pm    Post subject: Reply with quote

jmadero wrote:
Says the link is dead?? I was just about to try to change it in my spreadsheet and take a look at the second version and it says not a valid link...did you take it down? thanks again for all the help to both of you


No, it's still there and the link worked for me.
I will PM you my email address. If you can't get the link to work for you then just email me and I'll reply with it as an attachment.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group