[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
General User

Joined: 02 Jan 2009
Posts: 41

 Posted: Sat Feb 19, 2011 10:24 am    Post subject: Max Occurrence + Current "Streak" 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
ken johnson
Super User

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

Posted: Sat Feb 19, 2011 2:34 pm    Post subject:

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
_________________
ken johnson
Super User

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

 Posted: Sat Feb 19, 2011 6:39 pm    Post subject: 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
General User

Joined: 02 Jan 2009
Posts: 41

 Posted: Sat Feb 19, 2011 10:57 pm    Post subject: 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sun Feb 20, 2011 3:41 am    Post subject: 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.orgLast edited by Villeroy on Sun Feb 20, 2011 5:44 am; edited 3 times in total
ken johnson
Super User

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

Posted: Sun Feb 20, 2011 5:31 am    Post subject:

 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
_________________

Last edited by ken johnson on Sun Oct 14, 2012 3:29 am; edited 1 time in total
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sun Feb 20, 2011 5:54 am    Post subject: 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
ken johnson
Super User

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

Posted: Sun Feb 20, 2011 11:36 am    Post subject:

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.
Ken Johnson
General User

Joined: 02 Jan 2009
Posts: 41

 Posted: Tue Feb 22, 2011 11:28 am    Post subject: 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
ken johnson
Super User

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

Posted: Tue Feb 22, 2011 12:09 pm    Post subject:

 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
_________________