[Solved] Finding longest streak of numbers

[gyrojeremy]

Hi all,

I'm hoping I can find help here. I'm looking for a formula if possible to find the largest winning streak before a loss. The list would look something like this,
120
50
-60
70
10
100
50
-50
100
200
40

So the largest streak of this list would be between the negative numbers. So the answer would = 4 because there are 4 numbers, clear as mud?
The list can't be sorted and would have hundreds of entries.

[FJCC]

The attached file shows a solution with a helper column and the MAX() function. Note that the first row of the helper column( cell B1) uses a manual entry, not a formula. You could use

Code: Select all

=IF(A1>0;1;0)

in B1

[gyrojeremy]

That's great solution FJCC. Thanks, I'll try it out

[gyrojeremy]

Ok for some season when I drag the equation down the rest of the empty columns it counts up instead of remaining at 0 , which gives me an end result of how far I drag the equation down the empty column. I understand how your solution works and it works in your example perfectly but when it's transferred to my spreadsheet it counts up?

[robleyd]

Are you referring to the formula in C1 in FJCC's file? If you want B1:B11 to remain unchanged when dragged down, make the references absolute in C1.

[Tutorial] Absolute, relative and mixed references

[gyrojeremy]

I think the reason it's doing this is because the column in my spreadsheet (K5:K200) or (A1:A11 in FJCC example) has a formula related to other things. If I delete the formula in K column then the helper column reads 0 on all un-entered cells in K. I can't delete the formula in K column so could there be a work around?

[gerard24]

I think the reason it's doing this is because the column in my spreadsheet (K5:K200) or (A1:A11 in FJCC example) has a formula related to other things. If I delete the formula in K column then the helper column reads 0 on all un-entered cells in K. I can't delete the formula in K column so could there be a work around?

What formula ?
I won't be surprised if your formula returns "text" instead of numeric value...

[FJCC]

Uploading a small example file would help a lot. To upload a file, click Post Reply and then look for the Upload Attachment tab just below the box where you type a response.

[gyrojeremy]

I've not been successful in uploading the spreadsheet. I can't get the file small enough. The K column equation looks like this.
=IF(OR(ISBLANK(G45)=1,ISBLANK(H45)=1,ISBLANK(I45)=1,ISBLANK(J45)=1,ISERROR(M44)=1,ISBLANK(M44)=1),"",((H45*I45)-J45)*G45)

[robleyd]

Place your sample file on a file sharing site, such as Mediafire, and post a link here. The formula on its own doesn't indicate the type of value it returns.

[Villeroy]

Save a backup copy of your document (File>Save As... [Ctrl+Shift+S])
Reduce it to a few rows with the formula and the referenced cells.
Delete everything else that is not referenced by your formula cells.
We need to see the referenced cells of your formula. The actual cells on the sheet rather than a screen shot.

[gyrojeremy]

Got it sorted. Not ideal but it works. I got rid all this;
IF(OR(ISBLANK(G45)=1,ISBLANK(H45)=1,ISBLANK(I45)=1,ISBLANK(J45)=1,ISERROR(M44)=1,ISBLANK(M44)=1),"",
so now it's just this;
=(((H45*I45)-J45)*G45)
The empty cells in the K column are not empty anymore and contain $0.00 until I make an entry but it has allowed me to drag the helper column all the way to the bottom and get the desired result.
It upsets the helper column when I try to hide the zero's using ,"",