Forum Discussion

daniel95's avatar
daniel95
Copper Contributor
Oct 02, 2019

counting an the number of cells in which a run on 1s appears

I am creating a spread sheet for a stats class. I am simulating tossing a fair coin and having 1 appear if it is a head and 0 if it is a tail. I'd like to calculate the longest run of 1s in each row,...
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 02, 2019

daniel95 

For such sample

formula could be

=MAX(FREQUENCY( IF(A1:J1=1,COLUMN(A1:J1)),IF(A1:J1=0,COLUMN(A1:J1))))

That's array formula entered by Ctrl+Shift+Enter

  • daniel95's avatar
    daniel95
    Copper Contributor
    Oct 02, 2019
    Thanks Sergie. It is not quite working. I changed the As to Bs because my data starts in column B. Otherwise I just did a cut and paste. I get #Value! as error message. When I try to trace it I just get a line across row 1. Any thoughts?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 02, 2019

      daniel95 

      It works in next row as well. Do you use the formula as array one, with Ctrl+Shift+Enter?

      If you attache sample file it'll be easier to find the source of the issue.

Resources