Max/Frequency Question

[BobA]

I have the following array formula in a spreadsheet:

={((MAX(FREQUENCY(IF(Q894:Q7001>0,ROW(Q894:Q7001)),IF(Q894:Q7001<0,ROW(Q894:Q7001))))))}

It is used to determine the largest number of positive values in a column, (longest winning streak)
and it works fine. This column contains integers that I input, no formulas are used.

However, if I use the same formula in a different column, which is derived from formulas it doesn't work.

Can this formula be amended to solve the problem, or is an entirely different approach required?

Thanks,

Bob

 

[jasonb75]

Is it this one?

Formula Help

I have the following array formula in cell U22. =MAX(FREQUENCY(IF('4.6'!F5:'4.6'!F5005>0,ROW('4.6'!F5:'4.6'!F5005),""), IF('4.6'!F5:'4.6'!F5005*'4.6'!F6:'4.6'!F5005

www.mrexcel.com

 

[BobA]

Is it this one?

Formula Help

I have the following array formula in cell U22. =MAX(FREQUENCY(IF('4.6'!F5:'4.6'!F5005>0,ROW('4.6'!F5:'4.6'!F5005),""), IF('4.6'!F5:'4.6'!F5005*'4.6'!F6:'4.6'!F5005

www.mrexcel.com

Thanks for finding this. I'm working on it. I'll let you know if it works in a column derived from formulas.

 

[BobA]

Thanks for finding this. I'm working on it. I'll let you know if it works in a column derived from formulas.

I only needed one formula in a helper cell in D2:

=MATCH(9.99E+307,A2:A2000)

With that, the following formula in E2 will tell you your current winning or losing streak whether it be plus or minus:

=(D2-LOOKUP(2,1/(SIGN(A2:INDEX(A2:A1000,D2))<>SIGN(INDEX(A2:A1000,D2))),ROW(A2:INDEX(A2:A1000,D2))-ROW(A2)+1))*(sign(lookup(10^10,A2:A2000)))

[Entered as array]

It works when you input the data into column A or if you get your data from a simple formula.

It doesn't work if there are gaps or blank spaces.

It also doesn't work for the formulas I have in a column that derives it's answers from two other columns one of which is dates.

=if(B2<>B3,sumif(B:B,B2,C:C),"")

Try as I may I couldn't get it to work.

Stuff like this is simply above my pay grade.

Thanks for finding this. I'm working on it. I'll let you know if it works in a column derived from formulas.

 

[jasonb75]

It doesn't work if there are gaps or blank spaces.

How should gaps / blanks be handled? If there is a gap in the middle of a streak, should it

-End the current streak?
-Be counted as part of the streak if it is a unique date? (continue streak, blank counts as 1)
-Be ignored? (continue streak, blank counts as 0)

The earlier formulas that I suggested would have done the same as your original formula, which I think ignored the blanks and continued after but I want to be sure that I'm following it correctly before attempting this one.

Finally, do we need to keep the formula compatible with excel 2000, or is 2010 only acceptable?

 

[BobA]

2010 is fine, or whatever is easiest for you. I'm adaptable and can usually make things work.

As far as blanks are concerned, my screenshot shows where and why there are blank cells in the "Daily Result" column.

I enter the date and the results, however many there might be, for each particular day, and the "Daily Result" column automatically tabulates the sum total for each individual day as I go along.

There are formulas in every cell in the "Daily Result" column, but only the latest entry for a particular date will show a total.

My formula (and yours) work for the result column, if that is what I wish to do, no problem. But I'm trying to get it to work for the "Daily Result" column (in my screenshot.) So the one formula I'm trying to create (which I had and lost) would show my current win steak whether it be plus or minus.

In my screenshot, it would simply say 3. But it could just as easily be -3, 4, -5, 1, etc.

I hope I explained it well.

 

[jasonb75]

I might be missing something but I'm thinking that it should just be a case of finding the last daily result, then taking the latest positive or negative based on that result.

Something like

=IF(LOOKUP(1E+100,M8:M15)<0,X1,Y1)

Where X1 refers to the current negative streak formual, and Y1 the current positive streak formula.

If you are trying to do it with 1 formula instead of 3 then it should still work if you enter the current formulas directly into this one instead of referring to the cells. Remember to array confirm it if you do it that way.

Either way, it should be within the limits of excel 2000.