title: For dates with missing data, take average of values from closest dates
body: <p>I have sporadically collected data for stream nitrogen levels. For dates where nitrogen levels are not available, I'm hoping I can use an Excel formula that will estimate the value by calculating the average of nitrogen levels from the two closest dates.Note that nitrogen is not correlated with flow (or other water quality parameters) and there is no trend so I can't use a regression equation to estimate values. </p>
  
  <p>Below is an example of what the table looks like for columns A, B, and C, and rows 1 (header) to 7. Column B contains the real data (note that I inserted 'no value' for illustrative purposes). Column C is what I would like to have calculated. Because I have thousands of rows of data, manually entering each of these formulas is not an option.   </p>
  
  <p>Date_______Actual Value______Desired Calculation</p>
  
  <p>1/1/2012_______0.15__________=B2</p>
  
  <p>1/2/2012_____[no value]_______=AVERAGE(B2,B5)</p>
  
  <p>1/3/2012_____[no value]_______=AVERAGE(B2,B5)</p>
  
  <p>1/4/2012_______0.17__________=B5</p>
  
  <p>1/5/2012_____[no value]_______=AVERAGE(B5,B7)</p>
  
  <p>1/6/2012_______0.23__________=B7</p>