Replacing missing values with nearest known

[applebanana]

Hello,

I am trying to find a formula to replace missing values. Please find attached a document for illustrative purposes.

Sheet1 is the original data sheet, in Sheet2 I would like to have the same values as Sheet1 but replace the BLANK or 0 cells with the nearest known value in the column.

Any ideas? Thank you

[Richard Buttrey]

Please define nearest value. Nearest to what?

Manually add some typical results and explain how you have arrived at them.

[jason.b75]

How do you define nearest known value?

Take from above, take from below, average of both, something else entirely?

[applebanana]

Yes, if you look at the document, when the value is missing in the second row it should look from row 3 onwards for the nearest known value; when it is missing in row 30 it should look back at 29 or even further; when it is missing somewhere in between row 3 and 30 it should choose the nearest value above or below, when both are the same distance it should choose the one above.

[Phuocam]

Try:

=IF(Sheet1!A2>0,Sheet1!A2,
IF(ROW(Sheet1!A2)=2,Sheet1!A3,
IF(AND(Sheet1!A3=0,Sheet1!A1>0),Sheet1!A1,
IF(ROW(Sheet1!A2)-LOOKUP(10^10,ROW(Sheet1!A$1:A1)/(Sheet1!A$1:A1>0))<=MATCH(TRUE,INDEX(Sheet1!A3:A$31>0,0),0),
LOOKUP(10^10,Sheet1!A$1:A1/(Sheet1!A$1:A1>0)),INDEX(Sheet1!A3:A$31,MATCH(TRUE,INDEX(Sheet1!A3:A$31>0,0),0))))))

[applebanana]

Thank you, this works for me. The only issue might be in cases when there are multiple rows at the begging or end (e.g. A2:A3 | A29:A30) with missing values