Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Calculations using the Tabulate function

Topic Options

- 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 7, 2010 8:29 AM
(4700 views)

I've been using the Tabulate function to provide summaries of data tables. I'm trying to compare the % change between two sums for multiple groups.

Is there a way to add a simple calcuation of two columns summed in Tabulate, like column A / column B? I know I can make this into a data table, but I wanted to include it in a presentation and the formatting looks much better in the Tabulate window.

Here's a simple example pasted from the Tabulate dialog:

region brand Sum Sum

AU Clinique 104119 103289

Estee Lauder 58009 56009

Jo Malone 30125 29535

La Mer 16895 16730

MAC 106306 111065

Is there a way to add a simple calcuation of two columns summed in Tabulate, like column A / column B? I know I can make this into a data table, but I wanted to include it in a presentation and the formatting looks much better in the Tabulate window.

Here's a simple example pasted from the Tabulate dialog:

region brand Sum Sum

AU Clinique 104119 103289

Estee Lauder 58009 56009

Jo Malone 30125 29535

La Mer 16895 16730

MAC 106306 111065

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

For this specific example where Mean(.5 x Column) = .5 Mean(Column) that feature also lets you give custom formulas as well. Probably won't work for everything but you might be able to still use the functionality.

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

This answer requires you to create an extra column in your data table but it might be helpful for you.

Have you tried the Statistical - Col Mean function under column formulas? It has a little ^ symbol which means if you add a comma after the name of the column you wish to summarise you can add by variables. Assuming the column you are summarising is called Y.

Col Mean(:Y, :Item)/2

Would give you the summary statistic you desired. Then in tabulate the mean, min or max or this column by Item will give the result you require and you can edit the column text as desired.

I often use this in combination with if statements in my main table so each summary value for each group only appears once in each column. It is the JMP equivalent of SUMIF, AVERAGEIF, COUNTIF, etc.

9 REPLIES 9

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

Re: Calculations using the Tabulate function

I think I understand what you are asking about. You want to know if there are formula-like capabilities in Tabulate. Unfortunately, at time, there is not. The only thing you can do is calculate summary statistics. You can't specify custom formulas like you can in column formulas. But, I think it's a great idea!

If the nice formatting is what you are after, then the best option now is to create the calculations in a table (as you mentioned), then make the table into a Journal by using the**Journal** command on the Edit menu. Then right click on the resulting journal and there are a few format options under Table Style.

If the nice formatting is what you are after, then the best option now is to create the calculations in a table (as you mentioned), then make the table into a Journal by using the

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

Re: Calculations using the Tabulate function

Resurrecting this question: has there been any update in the last 5 and a half years? I was looking for a way to add some arithmetic to a mean but I can't figure out how to do this other than create a new table based on the tabulate. Which obviously kills the interactivity.

Thanks

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

Re: Calculations using the Tabulate function

I might not have you entirely understood. But if you select a few columns in the collistbox, then right click them you get an assortment of functions you can do. For instance you can go to combine>Sum and it will give you another column to choose from.

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

Re: Calculations using the Tabulate function

Sep 21, 2015 1:25 PM
(4368 views)
| Posted in reply to message from vince_faller 09/21/2015 03:55 PM

My fault for a poorly worded question, I should have been specific in the first place: what I would like to do is end up with 50% of the mean, e.g.

Item Mean 50% of Mean

A 20 10

B 15 7.5

I can do this by using Make Into Data Table and then creating a new column with a formula, but it would be nice if that formula could just be an option in the Tabulate.

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

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

Re: Calculations using the Tabulate function

Sep 22, 2015 7:10 AM
(4369 views)
| Posted in reply to message from vince_faller 09/21/2015 05:04 PM

I had trouble understanding this at first, but I did get it to work. Thanks!

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

This answer requires you to create an extra column in your data table but it might be helpful for you.

Have you tried the Statistical - Col Mean function under column formulas? It has a little ^ symbol which means if you add a comma after the name of the column you wish to summarise you can add by variables. Assuming the column you are summarising is called Y.

Col Mean(:Y, :Item)/2

Would give you the summary statistic you desired. Then in tabulate the mean, min or max or this column by Item will give the result you require and you can edit the column text as desired.

I often use this in combination with if statements in my main table so each summary value for each group only appears once in each column. It is the JMP equivalent of SUMIF, AVERAGEIF, COUNTIF, etc.

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

Re: Calculations using the Tabulate function

Sep 22, 2015 7:14 AM
(4368 views)
| Posted in reply to message from stephen_pearson 09/22/2015 02:51 AM

This one worked as well, and though it requires an extra step, it's more general. Plus, I learned that you can do by variables within certain functions. So, thanks to you, too! Unfortunately, it means you need to know what by variables you want prior to making the Tabulate, which again, sort of kills the interactivity and what I really like about the Tabulate function. But cie la vie.

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

Re: Calculations using the Tabulate function

In JMP 11 and 12 you can right click on a numeric column name in the column list of tabulate (and graph builder). If you select formula from the menu you can add the formula column required without leaving tabulate.

Sorry should have pointed that out - I am so used to it being there.