Finding location (or start cell) of highest consecutive values

  • Thread starter Thread starter Emilio Guerra
  • Start date Start date
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.
 
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.
Click to expand...

Hi
This would give you the Row number, if it's what you want.
=MATCH(MAX(G2:G50380),G2:G50380,0)
 
Also this would take you to it.
=HYPERLINK("#G"&MATCH(MAX(G1:G50380),G1:G50380,0),"GOTO:")
 
Also this would take you to it.

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

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?
 
Back
Top