Get the date from a run of cells from a Frequency formula

[Philb1]

Hello
I have a Frequency formula which calculates the longest continuous run of cells with a value greater than zero. No problem there.
Because it's possible for the run to continue over a number of days, I want to be able to get the date at the beginning of the run. The last date would be good as well provided it doesn't make the formula so complicated only a rocket scientist could work it out. I've tried index/match with no joy, which I presume is because Frequency returns an array & Index is no good for that.
The top formula below is the continuous run formula. The lower formula is my attempt to extract the date, both are array formulas & use named ranges.
I want to be able to do the same with a continuous run of zeros too, but I'll work that out from any help I get here.
I've attached an example of what I'm trying to achieve.

Thanks

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jhren]

Easy peasy... with a couple helper columns.

[benishiryo]

hi Philb1. try this:

Please Login or Register  to view this content.

[Philb1]

Thanks jhren

I was trying to keep away from helper columns, but, I could use your idea with VBA, delete the columns content afterwards & leave the date values. I get the named ranges from VBA so it won't be a biggie to add.
Cheers
Phil

[Philb1]

Thanks benishiryo

That's exactly what I'm after. Comparing yours & my formulas, I can see that I had the idea & should have matched the result rather than leaving the Large function in.

Thanks for your help
Phil

[jhren]

hi Philb1. try this:

Please Login or Register  to view this content.

Thanks benishiryo

That's exactly what I'm after. Comparing yours & my formulas, I can see that I had the idea & should have matched the result rather than leaving the Large function in.

Thanks for your help
Phil

I tried that and thought it didn't work. But now I see it works but only it gives the date at the end of the run. To get start date...
{=INDEX(ERange,MATCH(C5,FREQUENCY(IF(LRange>0,ROW(LRange)),IF(LRange<=0,ROW(LRange),0)),0)-C5)}

[Philb1]

Sorry for the delay in getting back here

Thanks jhren, that formula does work as you say

benisiyryo.. Your formula gave the result for the row after the run of cells. If there were 14 rows in a column with a value greater than zero, the formula gave the date for the 15th row.
Adding offset to your formula overcame that issue

Please Login or Register  to view this content.

Thank you both for your help & rep has been done for both of you

Cheers

I tried that and thought it didn't work. But now I see it works but only it gives the date at the end of the run. To get start date...
{=INDEX(ERange,MATCH(C5,FREQUENCY(IF(LRange>0,ROW(LRange)),IF(LRange<=0,ROW(LRange),0)),0)-C5)}