Excel 2013
I have a large set of model output (215K rows) Column A is Julian date at approximately 2 hour increments, column B is depth in approximate 0.6 m depths, but with variable starting depth.(Could be 0.1, 0.7, 1.3...
or could be 0.05, .65, 1.25....
or could start with a different small value)
The data are grouped with date in ascending order, then with depth in ascending order. For each date, there are always the same number of depths, but their precise values vary slightly. I want to compare rows for each date that are closest to my calibration date, which were collected at depths of 1.5, 3, 6, 12, 18 and 55.
One thought is to create column C which indicates which row contains a value in the depth field closest to one of several key depths among all measurements on the same date.
For this explanation, imagine I want to find the rows with depths closest to 1.5 and 3:
ColA ColB ColC
70.001 0.322 -
70.001 0.949 -
70.001 1.559 1.5 <--Selected because 1.559 is closer to 1.5 than is any other depth with date=70.001
70.001 2.169 -
70.001 2.779 3 <--Selected because 1.2.779 is closer to 3 than is any other depth with date=70.001
70.001 3.389 -
70.001 3.999 -
[54 rows trimmed]
70.084 0.443 -
70.084 1.191 -
70.084 1.801 1.5
70.084 2.411 -
70.084 3.021 3
70.084 3.631 -
70.084 4.241 -
70.084 4.851 -
[54 rows trimmed]
70.167 0.382 -
70.167 1.069 -
70.167 1.679 1.5
70.167 2.289 -
70.167 2.899 3
70.167 3.509 -
70.167 4.119 -
70.167 4.729 -
[54 rows trimmed]
70.25 0.485 -
70.25 1.276 1.5
70.25 1.886 -
70.25 2.496 -
70.25 3.106 3
70.25 3.716 -
70.25 4.326 -
70.25 4.936 -
[215200 rows trimmed]
I have a formula that works. Here's a version that would just identify the depth closest to 1.5: (version for row 6)
IF(AND(ABS(B6-1.5)<1,ABS(B6-1.5)<ABS(B7-1.5),ABS(B6-1.5)<ABS(B5-1.5)),1.5, "")
Basically, if a given row's depth is less that 1 away from 1.5, and it is closer to 1.5 than either the preceding or following row, we get a hit. It works fine, but if I set up nesting to pick out the other key depths, I end up with a very long formula:
=IF(AND(ABS(B6-1.5)<1,ABS(B6-1.5)<ABS(B7-1.5),ABS(B6-1.5)<ABS(B5-1.5)),1.5, IF(AND(ABS(B6-3)<1,ABS(B6-3)<ABS(B7-3),ABS(B6-3)<ABS(B5-3)),3, IF(AND(ABS(B6-6)<1,ABS(B6-6)<ABS(B7-6),ABS(B6-6)<ABS(B5-6)),6, IF(AND(ABS(B6-12)<1,ABS(B6-12)<ABS(B7-12),ABS(B6-12)<ABS(B5-12)),12, IF(AND(ABS(B6-18)<1,ABS(B6-18)<ABS(B7-18),ABS(B6-18)<ABS(B5-18)),18, IF(AND(ABS(B6-30)<1,ABS(B6-30)<ABS(B7-30),ABS(B6-30)<ABS(B5-30)),30, IF(AND(ABS(B6-55)<1,ABS(B6-55)<ABS(B7-55),ABS(B6-55)<ABS(B5-55)),55,"")))))))
This formula works, but it is very ugly and a little slow to compute. I am posting because I wonder if anyone has an idea for a more elegant and/or more efficient approach.