Formulas for conditionally replicating values needed

[schill2]

Hi,
Can anyone look inside my file and see if they can produce formulas to answer these questions?

Max number of Consecutive +1 values
Max number of Consecutive -1 values
Frequency of 1 Consecutive -1 values
Frequency of 2 Consecutive -1 values
Frequency of 3 Consecutive -1 values
Frequency of 4 Consecutive -1 values
Frequency of 5 Consecutive -1 values


Thank you very much for trying!

[benishiryo]

hi schill. it would be good if you can manually type out what you are hoping to see next time, so that we can know if we're on the right track. all these are array formulas:

D1
=MAX(FREQUENCY(IF($A$2:$A$200=1,ROW($A$2:$A$200)),IF($A$2:$A$200<>1,ROW($A$2:$A$200))))

D2
=MAX(FREQUENCY(IF($A$2:$A$200=-1,ROW($A$2:$A$200)),IF($A$2:$A$200<>-1,ROW($A$2:$A$200))))

D3 & below are another set
=SUM(IF(FREQUENCY(IF($A$2:$A$200=-1,ROW($A$2:$A$200)),IF($A$2:$A$200<>-1,ROW($A$2:$A$200)))=1,1))
just change it to 2-5 for the next few rows. if you have many, then use ROWS to help you make it incremental. for eg.
=SUM(IF(FREQUENCY(IF($A$2:$A$200=-1,ROW($A$2:$A$200)),IF($A$2:$A$200<>-1,ROW($A$2:$A$200)))=ROWS(D$3:D3),1))
copying that down will make it 2, 3, 4 & so on..

...all of the formulas to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.