Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Please help- Finding seperate averages for spans of data

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Please help- Finding seperate averages for spans of data

Apr 16, 2019 5:49 PM
(1750 views)

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.

4 REPLIES 4

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 |

Highlighted
##

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Article Labels

There are no labels assigned to this post.