Formula that will select the next nearest value 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 included an excel workbook containing an example of the data of interest in a thread below.


I hope I have explained myself sufficiently. Thank you for your help

[mrice]

Can you attach the workbook itself? The screen shot is unreadable and we all like to avoid retyping

[lakesgirl]

Hi,

I have attached here as a PDF and as .xlsx. I hope this helps. Thanks very much!!