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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Partion average by column value

- 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

Jan 30, 2014 7:20 AM
(714 views)

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

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

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

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 )

)