193225_en-US

liliconcom

Please help- Finding seperate averages for spans of data

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.

Re: Please help- Finding seperate averages for spans of data

It would be very helpful to see a sample data table and what you expect the solution to be.

Re: Please help- Finding seperate averages for spans of data

SpanRow	InverseSpanRow	DataPoints	InverseSpanAverages
missing	1	.5	.065
missing	1	.75	.065
3	missing	.8	missing
3	missing	.6	missing
3	missing	.9	missing
missing	6	.34	.385
missing	6	.43	.385

Re: Please help- Finding seperate averages for spans of data

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.

Re: Please help- Finding seperate averages for spans of data

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"
);