| View previous topic :: View next topic |
| Author |
Message |
jmadero
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 |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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 |
|
| Back to top |
|
|
jmadero
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. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 http://forum.openoffice.org
Last edited by Villeroy on Sun Feb 20, 2011 5:44 am; edited 3 times in total |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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
_________________
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 http://forum.openoffice.org |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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.
Link updated yet again.
Ken Johnson |
|
| Back to top |
|
|
jmadero
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 |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
|
