Formula to use previous value if not missing, otherwise use value from last occupied cell

[#1]

I'm trying to write a formula in Col3 that adds the current value of Col1 with the lagged value of Col2 but I'm running into a pickle trying to figure out how to carry forward the last observation from Col2 when missing data is encountered and to use this value at the first instance of non-missing data.  That is,

If( !IsMissing( Col1)
     Col1 + Lag(Col2,1);
     //create variable that contains current Col2 value;

//Else
     Col1 + previously stored value from Col2;

);

I would appreciate any suggestions on how best to accomplish this.  Thanks.

Row#	Col1	Col2	Col3
1	2	1
2	1	3	2
3	1	5	4
4	2	7	7
5	.	.
6	.	.
7	.	.
8	2	8	9
9	2	9	10
10	1	11	10
11	.	.
13	.	.
14	2	12	13
15	2	13	14
16	1	14	14
17	2	15	16

[#2]

Thanks Jerry.  This does look like exactly what I need.

Unfortunately I am a novice at this and I still need help figuring out how I can re-create this myself in my own database and the formula editor. I have attached a very simplfied version of the column's of interest for a single patient in my data.

In my database there are many rows of data with lab values that we are following, however, only some of the rows have a biopsy result. I want to create a formula that will use the dates in the "Dates" column to calculate the date difference between the biopsies whenever they occur.

Unfortunately I don't understand which functions you have used to create your formula.... for example... although if have used the "Is missing" function many times I don't even see the option of "Loc Nonmissing" in the formula editor.

Also, what does "nm" mean.... I assume "something matrix" but where do I find that in the formula editor?

Sorry for the hassel but I am axious to learn this as it looks like exactly what I need.

thanks again,

CW 

[#3]

Sorry I didn't provide more of an explanation in my previous response. First, to make the formula work for your data table, change the "Date" column in the "If" statements to "Biopsy Result" and the "Hour" argument in the Date Difference function to whatever interval you need (i.e. "Day"):

If( Row() == 1,
	nm = Loc Nonmissing( :Biopsy Result << get values );
	.;
,
	If( Is Missing( :Biopsy Result ),
		.,
		Try( Date Difference( :Date[nm[Contains( nm, Row() ) - 1]], :Date, "Day" ) )
	)
)

Now for the explanation. The existing formulas don't automatically index backward or forward until they find non-missing data, so I resorted to scripting to create variables that contain the information needed. If you would like to learn more about scripting, the Help->Books->Scripting Guide and Help->Scripting Index are great resources. 

The ":Biopsy Result<<Get Values" statement generates a list with all the values for Biopsy Result. Loc Nonmissing is a matrix function that finds all the positions in the list that are non-missing. The variable, "nm" stores this result, which now contains the row numbers for the non-missing Biopsy Result entries. Since this only needs to be created once, it is done for the first row only. Also, the result is set to missing for the first row.

Next, if Biopsy Result is missing, the result is set to missing, otherwise, we need to reference the previous, non-missing Date for the Date Difference calculation. Contains(nm, Row()) finds the position of the current row in the "nm" matrix. Subtract 1 from this to find the position of the previous row in the "nm" matrix. This now is the index, i.e. row number, for the Date value in the row with the previous, non-missing Biopsy Result. The "Try" function ignores the error generated by the Date Difference function when there is no previous, non-missing row (i.e. the first instance of a Biopsy Result). 

A couple of things to keep in mind, this assumes your data are sorted by date within ID # (as in your example). Also, if you have multiple ID #'s in your data table, you may want to add another condition so that you're not comparing two different ID's. In this case, your formula/script might look something like this:

If( Row() == 1,
	nm = Loc Nonmissing( :Biopsy Result << get values );
	.;
,
	If( Is Missing( :Biopsy Result ),
		.,
		If( :ID # == :ID #[nm[Contains( nm, Row() ) - 1]],
			Try( Date Difference( :Date[nm[Contains( nm, Row() ) - 1]], :Date, "Day" ) ),
			.
		)
	)
)

I know this is a lot of info, but you did say you were "anxious to learn this"... hope this helps.

[#4]

Thank you Jerry for your great explaination and for anticipating my next question of incorporating the ID's!

[#5]

Have we always been able to have a column Formula be self-referential?  I never realized I could do this. 

[#6]

Thanks everyone for your comments and help. 

I think Jerry's suggestion works the best for me and didn't require the use of a local variable which is what I was wanting to use.

[#7]

You might find the Data Table Tools add-in helpful for such cases.