0

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.

4
  • 1
    If I understand you correctly, I think you are going at this the wrong way. Instead of making one super formula, break it apart as that will compute quicker. Create a column for each of your lookup values (1.5 , 3, 6, 12, 18, 55) Then in each of those columns compute the difference between the value in question and the header. In one more column, use a combination of a MIN formula and MATCH to find which value had the smallest distance and then you simply display that column header. Hope this makes sense
    – Eric F
    Nov 6, 2015 at 20:34
  • Thanks for the comment - I do understand your suggestion. I am hesitant to approach it this way, though, because I actually have 10 different depth columns (at different sites along a river), and a constituent reading column for each of the sites. Addressing the issue with multiple helper columns will grow this spreadsheet pretty quickly.
    – Adam
    Nov 6, 2015 at 20:54
  • 1
    Well then I would suggest using a VBA answer if you are open to that. VBA will be much more robust and quicker than formulas.
    – Eric F
    Nov 6, 2015 at 20:55
  • 1
    or hide your calculation columns on another worksheet to keep them hidden.. up to you
    – Eric F
    Nov 6, 2015 at 20:59

0

You must log in to answer this question.

Browse other questions tagged .