In Excel, how to fill blank cells in a column, with last preceding non-blank cell in row?

0
votes

I need a formula that will find the last non-blank cell in each row and use that value to fill blank cells in the same row at last column. Any cells with values in the last column will remain intact. I have not tried any formula yet. The figure below may explain better:

enter image description here

3
excel-formula
I doubt you can find a formula-based solution which writes to the same column from which it is supposed to read a value. I guess you will always come to a "circular reference" problem. I recommend you to calculate the number outside of your data area, like adding one more column with the calculated value. Why do you need it exactly like this? What do you want to achieve? Aren't you a victim of xyproblem.info ? – Honza Zidek
This is part of handling missing research data using the process of "last observation carried forward." I know exactly what I need to achieve as result - I just cannot find the right process. So you comment of me being a victim of XY-Problem is out of context. The data I have is massive spanning over 10 years so a manual solution is out of the question. I did think of creating an additional column and populate it outside the data area. But again I need help in coming up with a workable solution. If a formula seems impossible, please inform me where I post this question. Thank you – Anthony Cutajar

3 Answers

0
votes

Normally a formula cannot refer to the cell in which the formula is. It leads to the Circular Reference error.

I am not sure if you can reach the desired result using iterative calculation (which kind of allow circular references), but the general purpose of iterative calculation is for numerical methods, not for searching for the last cell in a row :)

How are your cell values filled?

  • If you fill them programmaticaly, you know what is in your data and you may calculate the last column in the programmatical process.
  • If you fill them manually, you can live with the extra last column, which you then copy over to last column (Paste Values Only).

Or, as you properly named it in your comment, you may use the "last observation carried forward" approach:

  1. Fill all the cells with formula "copy the value from the left".
  2. Put your data where you do have them (i.e. overwrite the formula with actual data), leave your formula in the "empty" cells where you don't have the data.
  3. As a result, in the last column you will have either your formula carrying the last known value, or the number you entered there.

It's difficult to give you a better advice without knowing the whole process - I still suspect a kind of the X-Y problem, you have not persuaded me enough :) It is clear that you want the last measured value, but is a self-referencing formula in the last column really the only way to achieve your goal? Cannot you look at your problem out of the box and solve it otherwise?

0
votes

You can create another sheet and use something like the following to fill the blanks.

Sheet1 (Data)

A B C D E F G H I
12 14

Sheet2 (Output)

A B C D E F G H I
=Sheet1!A1 =IF(Sheet1!B1,Sheet1!B1,A1) =IF(Sheet1!C1,Sheet1!C1,B1) =IF(Sheet1!D1,Sheet1!D1,C1) -> Drag till end

which will result in something like this

A B C D E F G H I
12 12 12 14 14 14 14 14 14
0
votes

You could use this formula in the empty cells of column L.

=LOOKUP(2,1/(A2:K2<>""),A2:K2)

To enter it in the blank cells select column L, go to Find & Select> Go To Special...>Blanks, enter the column in the formula bar and commit it with CTRL+ENTER.