Dynamic cell reference - VLOOKUP

[Calum]

Hello,

I need to populate cell values for blank month entries by taking the average of the two months that fall either side of the blank entry.

The troubling part is that there may be one or more blank entries lumped together for one or more successive months. My attempt at tackling this was to use =AVERAGE and identify the last non-blank entry for the range of rows above each missing row, and the first non-blank entry for the rows below each missing row.

My attempts to find the last non-blank entry above the row have been along the lines of:

=LOOKUP(2,1/("C2:"&"C"&ROW()<>""), ("C2:"&"C"&ROW()))
https://exceljet.net/formula/get-val...non-empty-cell

The concatenated string that is used for the referencing does not read into the lookup as a cell reference however

Any help appreciated!

 

[steve the fish]

In this case it would be useful to see sample data with expected results.

 

[Calum]

Hi steve,

Apologies for the delay but I worked it out in the end. I used the strategy as I had listeed before and defined some helper columns which were then referenced using INDIRECT().

Thanks!