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

 

[BobA]

You had the right idea, but you need to reverse the arrays rather than just the symbols.

=MAX(FREQUENCY(IF(M8:M15<0,K8:K15),IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15))))

Got it, thanks again. I really appreciate it.

 

[BobA]

One more question: How would you alter the formula to show the current winning (or losing) streak. I've been trying to figure it out for several days, and I came up with two formulas that work in a column that isn't derived from formulas, but I can't come up with a solution for what I have.

 

[jasonb75]

Not sure if this will work without setting up a test sheet.

=LOOKUP(2,1/(1/FREQUENCY(IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15)),IF(M8:M15<0,K8:K15))))

 

[BobA]

Not sure if this will work without setting up a test sheet.

=LOOKUP(2,1/(1/FREQUENCY(IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15)),IF(M8:M15<0,K8:K15))))

Sorry, but I couldn't quite get this one to work.

 

[jasonb75]

What happened, or didn't happen?
Wrong result? No result? Formula not accepted by excel?

 

[BobA]

What happened, or didn't happen?
Wrong result? No result? Formula not accepted by excel?

Wrong result.

 

[jasonb75]

That likely means that it needs to grab the second last element of the frequency array rather than the last one.

Are you using excel 2000 as you mentioned in an earlier post, or excel 2010 as you have added to your account details? I'm not going to look at this in detail until the morning, but I suspect that attempting this with the limitations of excel 2000 might be difficult.

 

[BobA]

That likely means that it needs to grab the second last element of the frequency array rather than the last one.

Are you using excel 2000 as you mentioned in an earlier post, or excel 2010 as you have added to your account details? I'm not going to look at this in detail until the morning, but I suspect that attempting this with the limitations of excel 2000 might be difficult.

I was using 2000 on another computer, but I have 2010 on the one I'm using now.

 

[jasonb75]

I've just done a quick test and I think that I have found the problem, although further testing may prove different. The earlier formula would have either returned a #N/A error, or a count no greater than 2.

=LOOKUP(1e+100,1/(1/FREQUENCY(IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15)),IF(M8:M15<0,K8:K15))))

I had used a common method for finding the last entry in an array, but had forgotten to allow for variations between this and the way it is normally used.

 

[BobA]

I was using 2000 on another computer, but I have 2010 on the one I'm using now.

I've just done a quick test and I think that I have found the problem, although further testing may prove different. The earlier formula would have either returned a #N/A error, or a count no greater than 2.

=LOOKUP(1e+100,1/(1/FREQUENCY(IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15)),IF(M8:M15<0,K8:K15))))

I had used a common method for finding the last entry in an array, but had forgotten to allow for variations between this and the way it is normally used.

You are correct in that the first formula never returned a value greater than 2. However, I think the second formula nails it. I've tested it several times, and it works perfectly so far.

I spent all day on this trying several different things, but the fact is I never would have figured it out. Thanks again, much appreciated.

On a related note, I used to have a formula that does what you did above, only it showed the current streak whether it be positive or negative, all in one formula. I lost it when I transferred some things.

I seem to remember it being a max/frequency type of thing, but I don't recall exactly.

Any ideas? If it's too much work forget about it. You've been very generous with your time as it is.