| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding location (or start cell) of highest consecutive values
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)),I F(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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding location (or start cell) of highest consecutive values
On Thursday, January 23, 2014 8:26:34 AM UTC-5, Emilio Guerra wrote:
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)),I F(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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding location (or start cell) of highest consecutive values
Also this would take you to it.
=HYPERLINK("#G"&MATCH(MAX(G1:G50380),G1:G50380,0), "GOTO:") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding location (or start cell) of highest consecutive values
On Friday, January 24, 2014 10:22:18 AM UTC-5, Cimjet wrote:
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)),I F(G2:G50380=32.1,ROW(G2:G50380)))) Am I missing something? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding highest values | Excel Worksheet Functions | |||
| Finding records that have 12 consecutive values. | Excel Programming | |||
| Finding the highest values | Excel Discussion (Misc queries) | |||
| Finding and Formatting the 5 highest values in a range? | Excel Programming | |||
| Finding (Multiple) Highest Values in Column | Excel Worksheet Functions | |||
Linear Mode
