Calculate maximum number of consecutive populated cells in a column

Hi,

I'm new to the forum so apologies if this has been answered elsewhere.

Basically, I have two columns which are either populated with a 1 or are blank, lets call them columns A & B. I would like to calculate the maximimum run of consecutive 1's in column B and present that result in another sheet.

I would also like to calculate the maximum number of consecutive blanks in column B where column A is populated with a 1 and present that result in a different cell on another sheet.

Hopefully that's clear! Sorry if not, I'm fairly new to Excel.

Many thanks in advance!

Thanks for this but both formula's return the #VALUE! error... Any ideas? Both columns are populated with only 1's or blank cells apart from the top row. I altered the formula's to calculate from row 3.

Just a thought, could this be caused by the cells being populated with a dropdown from a list?

Originally Posted by Ace_XL

These are array formulas..

Confirm with Ctrl+Shift+Enter and not just Enter

Excellent! Thanks, that's done it! And thank you daddylonglegs for the formula's.

Much appreciated

In the actual sheet the formula looks like this: =MAX(FREQUENCY(IF(R3:R1002=1,ROW(R3:R1002)),IF(R3:R1002<>1,ROW(R3:R1002))))

What do I put in if I run this from sheet1 for sheet2? I've tried =MAX('Sheet2'!(FREQUENCY(IF(R3:R1002=1,ROW(R3:R1002)),IF(R3:R1002<>1,ROW(R3:R1002)))))
But that doesn't work. What am I doing wrong?

Many thanks! Should've tried that really, D'oh!