Please help- Finding seperate averages for spans of data

Question

Hi, I really need help trying to find seperate averages of data using a formula. I created a column that has a span of data, and I need to figure out the average of the data before that span starts (starting after the span before). I got an inverse span data column, but how can I seperate the data between spans to get an average for each section of data?

I tried to take the lag of up to five rows before the span starts, but it did not seem to work at all.

I am dying a little inside, please help.

ms · Answer

Try this column formula:

Col Mean(:DataPoints, :SpanRow || :InverseSpanRow);

Or if you, as in the sample table, wish the output in text format and "missing" spelled out:

If(:SpanRow == "missing",
    Char(Col Mean(:DataPoints, :InverseSpanRow)),
    "missing"
);

dale_lehman · Answer

I can't exactly tell what you want, but I think this should work: if you can select the spans of rows you want to average over (just select them - or you can use a formula if it will identify the rows you want to span), then Under Row Selection Name Selection in column will create a column of 0 and 1 for the two groups of rows (or you can name them something else). Then, create a formula using the Column Mean function and use a By variable of the column you just created.  This should give you the mean for each span of rows separately.

hmarek14 · Answer

SpanRowInverseSpanRowDataPointsInverseSpanAveragesmissing

1.5.065missing1.75.0653missing

.8missing3missing.6missing3missing.9missingmissing6.34.385missing6.43.385