Conditional Counting + Comparison

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Aug 17, 2014

• #2

4	0	0	0
3	0	0	0
-1	1	1	0
-2	1	2	2
3	0	0	0
4	0	0	0
5	0	0	0
6	0	0	0
5	0	0	0
4	0	0	0
3	0	0	0
-4	1	1	0
-5	1	2	0
-4	1	3	0
-3	1	4	0
-2	1	5	5
2	0	0	0
3	0	0	0
4	0	0	0
5	0	0	0
6	0	0	0
4	0	0	0
-2	1	1	1
3	0	0	0
4	0	0	0
	now use =large(D1:D25,1)
	to find biggest losing streak

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

Upvote 0

K

klosheen

New Member

Joined

Aug 15, 2014

Messages

18

• Aug 18, 2014

• #3

Hi oldbrewer,

I have tried what you suggested but it doesn't seem to work. I am not looking for the "biggest losing streak" ever, but for the last time the current losing streak has either been matched or surpassed. I am also ashamed to say I couldn't understand the table you provided - is it just a random example or does it relate to the one I had provided?

Many thanks for your input.

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Aug 18, 2014

• #4

my table demonstrates that you can first of all "mark" the negative numbers, then count how many in each block, then note how many in each block. If I added a date column I could tell you when the last time a run of "N"occurred

 

Upvote 0

K

klosheen

New Member

Joined

Aug 15, 2014

Messages

18

• Aug 18, 2014

• #5

oldbrewer said:

my table demonstrates that you can first of all "mark" the negative numbers, then count how many in each block, then note how many in each block. If I added a date column I could tell you when the last time a run of "N"occurred

Click to expand...

Does that mean I have to "mark" the negative values manually? That it is not very practical for what I want to do.

Anyway, thanks again for your help.

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Aug 18, 2014

• #6

Hi,

Is there any chance you can post some data which isn't a screenshot? Perhaps a smallish sample size together with your desired results.

Regards

 

Upvote 0

K

klosheen

New Member

Joined

Aug 15, 2014

Messages

18

• Aug 18, 2014

• #7

XOR LX said:

Hi,

Is there any chance you can post some data which isn't a screenshot? Perhaps a smallish sample size together with your desired results.

Regards

Click to expand...

Hi XOR LX,


That was my first choice but I am not allowed to post attachments. Will I be breaking any forum rules if I link to a Google Drive copy of the file?

Many thanks.

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Aug 18, 2014

• #8

klosheen said:

Will I be breaking any forum rules if I link to a Google Drive copy of the file?

Click to expand...

Not at all, though I was hoping more for a table such as oldbrewer pasted into the thread.

Regards

 

Upvote 0

O

oldbrewer

Well-known Member

Joined

Apr 11, 2010

Messages

11,012

• Aug 18, 2014

• #9

finding the negative numbers is done automatically by a formula in a helper column, other helper columns find each "negative run length". So if current negative run = 4 you only need to find the last time a run of 3 occurred - this is very easy to do - but is it what you want?

 

Upvote 0

K

klosheen

New Member

Joined

Aug 15, 2014

Messages

18

• Aug 18, 2014

• #10

XOR LX said:

Not at all, though I was hoping more for a table such as oldbrewer pasted into the thread.

Regards

Click to expand...

I have tried copying and pasting to mimic what oldbrewer did, but the formatting turns out all jumbled up.

Here's a link to the Excel file https://drive.google.com/file/d/0Bw3-U7VqHHqAYjVVSUlLODY4VG8/edit?usp=sharing

Cheers.

 

Last edited: Aug 18, 2014

Upvote 0