Hi All,
Please forgive me in advance, I am new to using forums and explaining my thoughts in terms of Excel jargon. I have a fairly large data set (141,884 rows x 4 columns) of oceanography data collected from an ongoing study from 2007-2011. The columns include: CRUISE ID (boat trip identification #), DEPTH (how far down the instrument was lowered into water), TEMP (water temperature at specified depth), and CHANGE (change in water temperature from previous measurement). An example is shown below of how the data are set up. The data are presented as increasing depth until the next cruise takes place.
I am attempting to find the thermocline (depth at which water temperature decreases rapidly) of the water for each cruise (n = 993). I am defining the thermocline at the depth (Column B) where the temperature change (Column D) is the greatest positive value for each cruise (Column A). In a Column F, I have listed each of the 993 cruises by their ID number and used the following formula in Column G: =INDEX(B:B, MATCH(MAX(IF(A:A=F2,D:D,0)),D:D,0)) and hit Ctrl+Shift+Enter afterwards. While the formula retrieves a number of depths associated with the maximum temperature change, I have several depths that are not correlated with the max value. Any ideas?
<tbody>
</tbody>
Thanks,
Connor
Please forgive me in advance, I am new to using forums and explaining my thoughts in terms of Excel jargon. I have a fairly large data set (141,884 rows x 4 columns) of oceanography data collected from an ongoing study from 2007-2011. The columns include: CRUISE ID (boat trip identification #), DEPTH (how far down the instrument was lowered into water), TEMP (water temperature at specified depth), and CHANGE (change in water temperature from previous measurement). An example is shown below of how the data are set up. The data are presented as increasing depth until the next cruise takes place.
I am attempting to find the thermocline (depth at which water temperature decreases rapidly) of the water for each cruise (n = 993). I am defining the thermocline at the depth (Column B) where the temperature change (Column D) is the greatest positive value for each cruise (Column A). In a Column F, I have listed each of the 993 cruises by their ID number and used the following formula in Column G: =INDEX(B:B, MATCH(MAX(IF(A:A=F2,D:D,0)),D:D,0)) and hit Ctrl+Shift+Enter afterwards. While the formula retrieves a number of depths associated with the maximum temperature change, I have several depths that are not correlated with the max value. Any ideas?
| A | B | C | D | E | F | G | |
| 1 | CRUISE | DEPTH | TEMP | CHANGE | CRUISE ID | THERMOCLINE | |
| 2 | 1021901 | 7 | 20.1737 | 0 | 1021901 | ||
| 3 | 1021901 | 8.9 | 20.1769 | -0.0032 | 1021902 | ||
| 4 | 1021901 | 10.9 | 20.1798 | -0.0029 | 1021903 | ||
| 5 | 1021901 | 12.9 | 20.1809 | -0.0011 | 1021904 |
<tbody>
</tbody>
Thanks,
Connor
