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

- JMP User Community
- :
- Discussions
- :
- Looking for script for new column formula for either geometric mean or 5% trimme...

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

Highlighted

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

Aug 29, 2019 3:57 PM
(3666 views)

My rows are products; my columns are ratings given by individual users. (The users saw most or all of the products).

I would like to create a column of either geometric or 5% trimmed mean of rating for each of my product (I don't want the arithmetic mean which is the only option under Formula--> Statistical).

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

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

Have you considered transforming your data in log space, then calculate the mean, and use the anti-log of the means?

Thierry R. Sornasse

Highlighted

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

Hi Thierry,

Thanks for your suggestion. Yes, that would work if all cells are filled. Your suggestion indeed is the formula in the Columns--> Combine--> New column--> formula geometric mean that Jeff suggests below to do.

I think what would work is a for loop script which does not count columns with no values. Sadly, I do not do jsl scripting :(

7 REPLIES 7

Highlighted
Have you considered transforming your data in log space, then calculate the mean, and use the anti-log of the means?

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

Thierry R. Sornasse

Highlighted

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

Hi Thierry,

Thanks for your suggestion. Yes, that would work if all cells are filled. Your suggestion indeed is the formula in the Columns--> Combine--> New column--> formula geometric mean that Jeff suggests below to do.

I think what would work is a for loop script which does not count columns with no values. Sadly, I do not do jsl scripting :(

Highlighted
##

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

Re: Looking for script for new column formula for either geometric mean or 5% trimmed mean

Thierry, with a few clicks and additional columns, I did get to a column of geometric means. It turns out that I didn't have to worry about missing values with this approach.

Thanks for this!

Highlighted
##

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

Re: Looking for script for new column formula for either geometric mean or 5% trimmed mean

You can easily make a formula for the geometric mean. It is simply the Nth root of the product of N values. Here is a picture of the formula for the case of four variables (X1-X4):

Learn it once, use it forever!

Highlighted
##

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

Re: Looking for script for new column formula for either geometric mean or 5% trimmed mean

Thanks, Mark. I hesitated to use this straight code since I have many columns and not all columns are filled.

Would be helpful for JMP to include these mean options in addition to arithmetic mean in New columns--> formula.

Highlighted
##

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

Re: Looking for script for new column formula for either geometric mean or 5% trimmed mean

Hello @TCM ,

Admittedly, it's a bit buried, but if you select all of the columns for which you want the geometric mean, right-click on one of them, then select New Formula Column->Combine->Geometric Mean, you may get what you're after. Some of the other options in the Combine menu may be helpful to construct other formulas when you have a lot of columns.

Hope this helps.

Highlighted
##

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

Re: Looking for script for new column formula for either geometric mean or 5% trimmed mean

Hi Jerry,

Thanks for the tip! Didn't know it was lurking there in the background. This is useful.

However, it only works when all my columns are filled. To get the mean on those rows with missing values, I would have to evaluate each row and make on the spot formula revisions, deleting cols with no values- which I may have to do if there is no other way around it. (Thinking there may be a special treatment of cols with missing values such that those cells are excluded from the calculation).

Thanks for the tip! Didn't know it was lurking there in the background. This is useful.

However, it only works when all my columns are filled. To get the mean on those rows with missing values, I would have to evaluate each row and make on the spot formula revisions, deleting cols with no values- which I may have to do if there is no other way around it. (Thinking there may be a special treatment of cols with missing values such that those cells are excluded from the calculation).

Article Labels

There are no labels assigned to this post.