Special formula

unknown

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

unknown

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.