Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-889197%22%20slang%3D%22en-US%22%3Ecounting%20an%20the%20number%20of%20cells%20in%20which%20a%20run%20on%201s%20appears%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889197%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20creating%20a%20spread%20sheet%20for%20a%20stats%20class.%20I%20am%20simulating%20tossing%20a%20fair%20coin%20and%20having%201%20appear%20if%20it%20is%20a%20head%20and%200%20if%20it%20is%20a%20tail.%20I'd%20like%20to%20calculate%20the%20longest%20run%20of%201s%20in%20each%20row%2C%20where%20a%20row%20represents%20100%20tosses%20of%20a%20coin.%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-889197%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889367%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20an%20the%20number%20of%20cells%20in%20which%20a%20run%20on%201s%20appears%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418885%22%20target%3D%22_blank%22%3E%40daniel95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20283px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135138i70FB7401F39AA36E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMAX(FREQUENCY(%20IF(A1%3AJ1%3D1%2CCOLUMN(A1%3AJ1))%2CIF(A1%3AJ1%3D0%2CCOLUMN(A1%3AJ1))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThat's%20array%20formula%20entered%20by%20Ctrl%2BShift%2BEnter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889622%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20an%20the%20number%20of%20cells%20in%20which%20a%20run%20on%201s%20appears%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889622%22%20slang%3D%22en-US%22%3EThanks%20Sergie.%20It%20is%20not%20quite%20working.%20I%20changed%20the%20As%20to%20Bs%20because%20my%20data%20starts%20in%20column%20B.%20Otherwise%20I%20just%20did%20a%20cut%20and%20paste.%20I%20get%20%23Value!%20as%20error%20message.%20When%20I%20try%20to%20trace%20it%20I%20just%20get%20a%20line%20across%20row%201.%20Any%20thoughts%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-889812%22%20slang%3D%22en-US%22%3ERe%3A%20counting%20an%20the%20number%20of%20cells%20in%20which%20a%20run%20on%201s%20appears%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-889812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F418885%22%20target%3D%22_blank%22%3E%40daniel95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20works%20in%20next%20row%20as%20well.%20Do%20you%20use%20the%20formula%20as%20array%20one%2C%20with%20Ctrl%2BShift%2BEnter%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20701px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135195i89A3C63DB89A906B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20attache%20sample%20file%20it'll%20be%20easier%20to%20find%20the%20source%20of%20the%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E
daniel95
New Contributor

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, where a row represents 100 tosses of a coin. Thanks.

3 Replies

@daniel95 

For such sample

image.png

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

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?

@daniel95 

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

image.png

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies