Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Special formula

Topic Options

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

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

Special formula

Nov 12, 2009 6:46 AM
(1105 views)

Hi!

I’m trying to establish a new column with a kind of „dynamic mean“ but I can’t manage!

In fact, my data table looks like this, containing three columns (just longer):

Person 1 Value1 1

Person 1 Value2 2

Person 2 Value1 1

Person 2 Value2 2

Person 2 Value3 3

Person 2 Value4 4

Person 3 Value1 1

Person 3 Value2 2

Person 3 Value3 3

Person 4 Value1 1

Person 5 Value1 1

Person 5 Value2 2

What I need is a formula that calculates the mean value derived by the values obtained until then. So, in all rows called “value 1” mean value is the same like value 1, in all rows called “value 2” mean value is formed by the first two values of this person and so on.

Can you help me?

Message was edited by: Steffi G

I’m trying to establish a new column with a kind of „dynamic mean“ but I can’t manage!

In fact, my data table looks like this, containing three columns (just longer):

Person 1 Value1 1

Person 1 Value2 2

Person 2 Value1 1

Person 2 Value2 2

Person 2 Value3 3

Person 2 Value4 4

Person 3 Value1 1

Person 3 Value2 2

Person 3 Value3 3

Person 4 Value1 1

Person 5 Value1 1

Person 5 Value2 2

What I need is a formula that calculates the mean value derived by the values obtained until then. So, in all rows called “value 1” mean value is the same like value 1, in all rows called “value 2” mean value is formed by the first two values of this person and so on.

Can you help me?

Message was edited by: Steffi G

2 REPLIES

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

Re: Special formula

It sounds like you want a cumulative mean. Consider the two columns below, a group column, and a data column:

**Group** **Y**

A 8

A 7

A 4

B 1

B 6

B 7

C 4

C 2

C 8

D 1

D 4

D 4

Within each group, I want to compute the mean of all the observations up to that point. When a new group is reached, the cumulative mean resets.

Create these two columns:

1. cumulative sum:

If( Row() == 1 | Group[Row()] != Group[Row() - 1], Y,

cumulative sum[Row() - 1] + Y[Row()])

2. cumulative denominator:

If( Row() == 1 | Group[Row()] != Group[Row() - 1], 1,

cumulative denominator[Row() - 1] + 1])

The cumulative sum formula works like this. JMP evaluates the formula for each row. If it is on row 1, OR, if the group in the current row is not equal to the group in the previous row, then just use Y as the cumulative sum. Else, take the value of cumulative sum in the previous row, and add to it the value of Y in the current row.

The cumulative denoiminator formula works similarly.

To get the cumulative mean, just divide the cumulative sum by the cumulative denominator.

Note, this only works if the data is sorted by group, and the Y values are in the order you want.

A 8

A 7

A 4

B 1

B 6

B 7

C 4

C 2

C 8

D 1

D 4

D 4

Within each group, I want to compute the mean of all the observations up to that point. When a new group is reached, the cumulative mean resets.

Create these two columns:

1. cumulative sum:

If( Row() == 1 | Group[Row()] != Group[Row() - 1], Y,

cumulative sum[Row() - 1] + Y[Row()])

2. cumulative denominator:

If( Row() == 1 | Group[Row()] != Group[Row() - 1], 1,

cumulative denominator[Row() - 1] + 1])

The cumulative sum formula works like this. JMP evaluates the formula for each row. If it is on row 1, OR, if the group in the current row is not equal to the group in the previous row, then just use Y as the cumulative sum. Else, take the value of cumulative sum in the previous row, and add to it the value of Y in the current row.

The cumulative denoiminator formula works similarly.

To get the cumulative mean, just divide the cumulative sum by the cumulative denominator.

Note, this only works if the data is sorted by group, and the Y values are in the order you want.

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

Re: Special formula

Ohhh!

It worked perfectly the way you explained!

Thank you Jonathan!

It worked perfectly the way you explained!

Thank you Jonathan!