Finding location (or start cell) of highest consecutive values

E

Emilio Guerra

Hello, I have a worksheet and I already know that the highest number of consecutive values of a range is 16.

This is the formula I used and it works:

=MAX(FREQUENCY(IF(G2:G50380<32.1,ROW(G2:G50380)),IF(G2:G50380>=32.1,ROW(G2:G50380))))

The returned value is 16, which is correct. So far, so good.


What I would like to know is WHERE is that range (location of the cell where that streak of 16 consecutive values under 32.1 starts and ends).


I've tried INDEX but that didn't work.

Thanks.
 
C

Cimjet

Hello, I have a worksheet and I already know that the highest number of consecutive values of a range is 16.



This is the formula I used and it works:



=MAX(FREQUENCY(IF(G2:G50380<32.1,ROW(G2:G50380)),IF(G2:G50380>=32.1,ROW(G2:G50380))))



The returned value is 16, which is correct. So far, so good.





What I would like to know is WHERE is that range (location of the cell where that streak of 16 consecutive values under 32.1 starts and ends).





I've tried INDEX but that didn't work.



Thanks.

Hi
This would give you the Row number, if it's what you want.
=MATCH(MAX(G2:G50380),G2:G50380,0)
 
C

Cimjet

Also this would take you to it.
=HYPERLINK("#G"&MATCH(MAX(G1:G50380),G1:G50380,0),"GOTO:")
 
E

Emilio Guerra

Also this would take you to it.

=HYPERLINK("#G"&MATCH(MAX(G1:G50380),G1:G50380,0),"GOTO:")

Thanks, Cimjet, but that doesn't seem to work with my formula. You gave me a formula that finds the MAX value, but I had a FREQUENCY in there too.

=MAX(FREQUENCY(IF(G2:G50380<32.1,ROW(G2:G50380)),IF(G2:G50380>=32.1,ROW(G2:G50380))))


Am I missing something?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top