Partion average by column value<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Hi there,
I know how create formula based on an column average but what I need to to is partition this by values in another column:
What I have so far is If(:Rank <= 2, Mean(:Residual), Empty()) which would give me the mean
With the table below I need to partition this to another level and average by the sub-value and the State. How would I modify the formula to achieve the required result in JMP?
Would i need to summarize the values in the columns that i need and use an array to complete this?
I have also multiple runs that are dynamic values - could be up to 5 entries in the run column or only 2....
| STATE | SUB_VALUE | Residual | Rank |
| RUN1 | ALL | 3.343158 | 1 |
| RUN1 | MEAN | 3.62092 | 1 |
| RUN1 | RANGE | -2.26072 | 1 |
| RUN1 | ALL | 3.141567 | 2 |
| RUN1 | MEAN | 3.415537 | 2 |
| RUN1 | RANGE | -2.47423 | 2 |
| RUN2 | ALL | 1.258086 | 1 |
| RUN2 | MEAN | 0.325492 | 1 |
| RUN2 | RANGE | -0.77308 | 1 |
| RUN2 | ALL | 2.328553 | 2 |
| RUN2 | MEAN | 0.184974 | 2 |
| RUN2 | RANGE | -0.88911 | 2 |
| RUN2 | ALL | -6.00891 | 4 |
| RUN2 | MEAN | -0.56991 | 4 |
| RUN2 | RANGE | -0.3764 | 4 |
| RUN2 | ALL | -6.1236 | 3 |
| RUN2 | MEAN | -0.26641 | 3 |
| RUN2 | RANGE | -1.49011 | 3 |
Rgds
Colin
Re: Partion average by column valueThe optional arguments of the Col Mean() function works as By-clauses.
Not completely sure of what you want to do, but this formula should give the mean for every level of STATE, SUB_VALUE and Rank<=2:
If( :Rank <= 2,
Col Mean( :Residual, :STATE, :SUB_VALUE, :Rank <= 2 )
)
