How to find the mean between rows by a group with missing data in a forward-looking format?

[#1]

I'm running into this issue where I have two sets of datapoints across my timeline. One is collected at every point in the interval "Temperature (Celsius)", the other is collected sporadically "Residual Glucose (g/L)". When placed together in a data table, one of the columns is missing data in the interval. I attached a data table as an example but essentially looks like this:

What I need is to get the mean of the "Temperature (Celsius)" column between rows that contain data in the "Residual Glucose (g/L)" column. The caveat is that this has to be forward-looking . E.g. at row 1, the result should just be "32".  At row 7, the result should be the mean of everything in [Row 2, Row 7] = "32.1". The results should show in the cells highlighted green.

I have been doing this by creating a helper column, using sort, and then tabulating but this is very time consuming. I am hoping to get help automating this as I keep adding data to my table. I would appreciate any help and/or guidance!

[#2 · Accepted solution]

If you already have done it manually, you could quite easily create workflow to automate it.

Here is formula which might do what you are looking for (I suggest breaking it into parts to make it easier to understand)

If(!IsMissing(:"Residual Glucose (g/L)"n),
	Col Mean(
		:"Temperature (Celsius)"n,
		Col Cumulative Sum(
			!Is Missing(Lag(:"Residual Glucose (g/L)"n)) & Is Missing(:"Residual Glucose (g/L)"n),
			:Batch ID
		),
		:Batch ID
	)
,
	.
);

[#3]

Just what I was looking for!! Thank you so much :)

[#4]

You can create a new column that adds a grouping column that has different group values for each of the non Residual Gluecose value rows.

Here is the formula for the calculation of the group variable

As Constant( x = 0 );
If( Is Missing( :"Residual Glucose (g/L)"n ) == 0,
	x
	++);
If( Is Missing( :"Residual Glucose (g/L)"n ) == 1,
	x
);

Then using Col Mean you can get the means you want

Here is the formula for the group mean calculation

If( Is Missing( :group ) == 0,
	Col Mean( :"Temperature (Celsius)"n, :group )
)