Formula that will select the next nearest value to a number from a row

[lakesgirl]

Hello there,

I need to run a Dixon's-Q Outlier test on lake water chemistry data for almost 800 lakes. Each lake has multiple years of data (3-10 years). This calculation requires knowing the absolute difference between the value in question and its next nearest value.

My problem: I am having quite a bit of trouble creating a formula that will select the next nearest value from a ROW that contains both the value being compared AND blank cells. I have tried variations of Index/Match, and HLookup, but have exceeded my knowledge of excel formulas.

I have pasted an example of the data of interest below. For example, Anstruther Lake station 96-1 has 7 readings between 2003-14. I want to select the next nearest value to the 2003 reading (5.8). Technically, this would be 5.8 (from 2006) but I need to automate this for ease over hundreds of values.
[TABLE="width: 1179"]
<colgroup><col><col span="14"><col></colgroup><tbody>[TR]
[TD]


LAKE_NAME[/TD]
[TD]STN[/TD]
[TD]Site ID[/TD]
[TD="align: right"]2003[/TD]
[TD="align: right"]2004[/TD]
[TD="align: right"]2005[/TD]
[TD="align: right"]2006[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD]Count of TP[/TD]
[/TR]
[TR]
[TD]ANSTRUTHER LAKE[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.8[/TD]
[TD] [/TD]
[TD="align: right"]5.3[/TD]
[TD="align: right"]5.8[/TD]
[TD] [/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]4.5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]7.3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]ANSTRUTHER LAKE[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12.0[/TD]
[TD] [/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]5.7[/TD]
[TD] [/TD]
[TD="align: right"]5.6[/TD]
[TD="align: right"]5.3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5.0[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]ARMSTRONG LAKE[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.3[/TD]
[TD="align: right"]12.0[/TD]
[TD="align: right"]10.9[/TD]
[TD="align: right"]9.4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]SPRUCE LAKE (ART)[/TD]
[TD="align: right"]127[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]7.5[/TD]
[TD] [/TD]
[TD="align: right"]5.9[/TD]
[TD="align: right"]6.3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]ASHIGAMI LAKE[/TD]
[TD="align: right"]132[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5.1[/TD]
[TD] [/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.1[/TD]
[TD="align: right"]7.4[/TD]
[TD] [/TD]
[TD="align: right"]4.7[/TD]
[TD="align: right"]5.0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]BAPTISTE LAKE[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9.1[/TD]
[TD="align: right"]9.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]9.1[/TD]
[TD="align: right"]8.7[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]BAPTISTE LAKE[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]13.8[/TD]
[TD="align: right"]11.8[/TD]
[TD="align: right"]9.7[/TD]
[TD="align: right"]11.0[/TD]
[TD="align: right"]9.2[/TD]
[TD="align: right"]9.2[/TD]
[TD="align: right"]10.7[/TD]
[TD] [/TD]
[TD="align: right"]10.5[/TD]
[TD="align: right"]10.6[/TD]
[TD] [/TD]
[TD="align: right"]17.2[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]BIG BARNUM LAKE[/TD]
[TD="align: right"]217[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10.1[/TD]
[TD="align: right"]15.5[/TD]
[TD="align: right"]9.1[/TD]
[TD="align: right"]5.8[/TD]
[TD="align: right"]5.9[/TD]
[TD="align: right"]10.1[/TD]
[TD="align: right"]10.4[/TD]
[TD="align: right"]7.0[/TD]
[TD="align: right"]7.2[/TD]
[TD="align: right"]8.5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]BASS LAKE[/TD]
[TD="align: right"]237[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6.7[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]8.8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]BASS LAKE[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6.1[/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]22.4[/TD]
[TD="align: right"]22.2[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]16.7[/TD]
[TD] [/TD]
[TD="align: right"]9.8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]BASSWOOD LAKE[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.2[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]3.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2.3[/TD]
[TD] [/TD]
[TD="align: right"]2.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]BAY LAKE[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]5.0[/TD]
[TD="align: right"]5.8[/TD]
[TD="align: right"]4.2[/TD]
[TD="align: right"]4.5[/TD]
[TD] [/TD]
[TD="align: right"]6.0[/TD]
[TD="align: right"]6.2[/TD]
[TD="align: right"]5.8[/TD]
[TD="align: right"]5.4[/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]BAY LAKE[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]5.8[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]4.2[/TD]
[TD] [/TD]
[TD="align: right"]5.9[/TD]
[TD="align: right"]7.0[/TD]
[TD="align: right"]5.2[/TD]
[TD="align: right"]5.5[/TD]
[TD="align: right"]5.5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]BEAR LAKE (NL)[/TD]
[TD="align: right"]274[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]10.1[/TD]
[TD="align: right"]11.6[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]10.3[/TD]
[TD="align: right"]12.6[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

I hope I have explained myself sufficiently. I hope that someone can help me figure this one out.

Thank you very much!!

 

[onlyadrafter]

Hello,

can do it with VBA.

Have assumed that you want to compare the first reading for a specific lake i.e. SPRUCE LAKE (ART) you would be looking to find the nearest value to 7.2 (2004 reading) and get the answer 7.2.

Try this code, not very elegant, but its very early in the morning.

Sub LAKE_DATA()
    For MY_ROWS = 2 To ActiveSheet.UsedRange.Rows.Count
        For MY_COLS = 4 To 15
            If Not IsEmpty(Cells(MY_ROWS, MY_COLS)) Then
                MY_VALUE = Cells(MY_ROWS, MY_COLS).Value
                MY_DIFF = MY_VALUE
                For MY_NEW_COLS = MY_COLS + 1 To 15
                    If Not IsEmpty(Cells(MY_ROWS, MY_NEW_COLS)) Then
                        MY_NEW_DIFF = Cells(MY_ROWS, MY_NEW_COLS).Value - MY_VALUE
                        If MY_NEW_DIFF < 1 Then
                            MY_NEW_DIFF = MY_NEW_DIFF * -1
                        End If
                        If MY_NEW_DIFF < MY_DIFF Then
                            MY_DIFF = MY_NEW_DIFF
                            MY_NEW_VALUE = Cells(MY_ROWS, MY_NEW_COLS).Value
                        End If
                    End If
                Next MY_NEW_COLS
            Range("S" & MY_ROWS).Value = MY_NEW_VALUE
            MY_COLS = 15
            MY_NEW_DIFF = 0
            MY_NEW_VALUE = 0
            GoTo CONT
            End If
        Next MY_COLS
CONT:
    Next MY_ROWS
End Sub