Max/Frequency Question

BobA

Board Regular
Joined
Nov 16, 2008
Messages
63
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
However, if I use the same formula in a different column, which is derived from formulas it doesn't work.
The same formula should work both ways, if it is not then there is likely a problem with the other formulas, or the data that they are sourcing from.
 
Upvote 0
The same formula should work both ways, if it is not then there is likely a problem with the other formulas, or the data that they are sourcing from.
Here is an example of the formula from the other column. This formula is in every cell of the column dragged all the way down.

[The "K" column referred to in this formula are dates.]

Also, I tried to do this on a new sheet with minimum data and it still didn't work.

=if(K2297<>K2298,sumif(K:K,K2297,Q:Q),"")
 
Upvote 0
As soon as I see the formula, the problem becomes immediately obvious.

Starting with the easiest fix, would 0 instead of "" (in the formula you just posted) be acceptable?

In formula terms, "" is > than 0, so all of the blanks would be considered as a positive value, so to work with that, you are right, the frequency formula would need to be rewritten.
If you do need a new formula for that, please confirm which version of excel you are using (preferably update your Account details to show this). I don' think that it will make any difference here, but often there are new functions in later versions of excel that can help to simplify formulas.


edit:- looking at the problem again, your formula is only ever going to give you 1 result per day, so are you looking for the number of consecutive days with a positive number, rather than just the count of consecutive positive numbers alone?
 
Upvote 0
As soon as I see the formula, the problem becomes immediately obvious.

Starting with the easiest fix, would 0 instead of "" (in the formula you just posted) be acceptable?

In formula terms, "" is > than 0, so all of the blanks would be considered as a positive value, so to work with that, you are right, the frequency formula would need to be rewritten.
If you do need a new formula for that, please confirm which version of excel you are using (preferably update your Account details to show this). I don' think that it will make any difference here, but often there are new functions in later versions of excel that can help to simplify formulas.


edit:- looking at the problem again, your formula is only ever going to give you 1 result per day, so are you looking for the number of consecutive days with a positive number, rather than just the count of consecutive positive numbers alone?
It might be easier to answer the second part of this and go from there. Yes. I am looking for the largest number of consecutive days with a positive number. {Excel 2000}
 
Upvote 0
A couple of things that I've noticed from a few quick tests with your formulas.

Zero values don't break the chain, only negative. Is this correct?
The SUMIF formula is placing the daily total against the last row for the date each time, all other rows are blank?

Is there data for every date? If there are missing dates, lets say a positive run goes from Jan 1, Jan 2, Jan 3, Jan 5, Jan 6. Should that be counted as 3, 5, or 6 consecutive days?

I think that this is going to be a challenge working with excel 2000, but defeat is not a word that I'm familiar with.
 
Upvote 0
This is what I've got so far based on a few assumptions.

For testing purposes, I had dates in K8:K15 and the SUMIF formula from post 3 in M8:M15. This needs to be array confirmed, same as your previous formula.
Excel Formula:
=MAX(FREQUENCY(IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15)),IF(M8:M15<0,K8:K15)))

Hopefully that will be somewhere close :)
 
Upvote 0
Solution
This is what I've got so far based on a few assumptions.

For testing purposes, I had dates in K8:K15 and the SUMIF formula from post 3 in M8:M15. This needs to be array confirmed, same as your previous formula.
Excel Formula:
=MAX(FREQUENCY(IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15)),IF(M8:M15<0,K8:K15)))

Hopefully that will be somewhere close :)
Thank you. It works, and I also checked it manually, so there is a match. However, I assumed it would be a very simple procedure, by just reversing the >< signs in the formula to <> to come up with an answer for the largest number of consecutive losing (negative) days, but that doesn't work.

You did come up with a solution for what I asked. No question. Is there a simple fix so I can get an answer to the opposite question?

Thanks again for your time.
 
Upvote 0
{Excel 2000}
Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

1617514490726.png
 
Upvote 0
Is there a simple fix so I can get an answer to the opposite question?
You had the right idea, but you need to reverse the arrays rather than just the symbols.
Excel Formula:
=MAX(FREQUENCY(IF(M8:M15<0,K8:K15),IF(ISNUMBER(M8:M15),IF(M8:M15>0,K8:K15))))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top