Subscribe Bookmark RSS Feed

Partion average by column value

lodey101

Community Trekker

Joined:

Oct 4, 2011

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....

STATESUB_VALUEResidualRank
RUN1ALL3.3431581
RUN1MEAN3.620921
RUN1RANGE-2.260721
RUN1ALL3.1415672
RUN1MEAN3.4155372
RUN1RANGE-2.474232
RUN2ALL1.2580861
RUN2MEAN0.3254921
RUN2RANGE-0.773081
RUN2ALL2.3285532
RUN2MEAN0.1849742
RUN2RANGE-0.889112
RUN2ALL-6.008914
RUN2MEAN-0.569914
RUN2RANGE-0.37644
RUN2ALL-6.12363
RUN2MEAN-0.266413
RUN2RANGE-1.490113

Rgds

Colin

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

The 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 )

)

1 REPLY
Solution

The 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 )

)