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

- JMP User Community
- :
- Discussions
- :
- How do I insert a row at the top of the table and compute the percentage of nega...

- 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 23, 2018 6:44 AM
(5756 views)

There are multiple rows and columns in the table. Can you insert a row above the first row and calculate the percentage of negative Numbers in the same column?The effect is shown below.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

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

Created:
Aug 24, 2018 3:13 AM
| Last Modified: Aug 24, 2018 4:03 AM
(5761 views)
| Posted in reply to message from Thierry_S 08-23-2018

Generally, it's a bad idea to have data of different levels of granularity held in the same table. Additionally (like other statistical software) JMP requires data in a column to be of the same data type, modeling type and format.

But, so long as you don't mind a proportion rather than a percentage, if you want to do this you could use JSL:

```
NamesDefaultToHere(1);
// (1) Make some data
nr = 10; // Number of rows
nc = 5; // Number of columns
dt = NewTable("Data");
for (c = 1, c<=nc, c++, dt << NewColumn("Col "||Char(c), Formula(RandomINteger(-10, 10))));
dt << addRows(nr);
dt << runFormulas;
for (c = 1, c<=nc, c++, Column(dt, c) << deleteFormula);
// (2) Insert a new first row
dt << addRows(1, atStart);
// (3) Populate values of first row
for (c=1, c<=NCols(dt), c++,
vals = Column(dt, c) << getValues;
vals = vals[2::NRows(dt)];
Column(dt, c)[1] = NRows(Loc(vals < 0))/(NRows(dt)-1);
);
```

7 REPLIES 7

Highlighted
##

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

Re: How do I insert a row at the top of the table and compute the percentage of negative Numbers for

It's done with JSL, thanks!

Highlighted
##

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

Re: How do I insert a row at the top of the table and compute the percentage of negative Numbers for

The new inserted row format may not be in the percentage format.

Highlighted
##

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

Re: How do I insert a row at the top of the table and compute the percentage of negative Numbers for

Hi,

This might not be the most efficient way to handle your specific issue but unlike Excel, JMP does not easily allow for calculations in specific rows as you mentioned. Hence, would it be an option to transpose your table (columns A, B, C, D become rows with 11 columns of data) in which you could add an Average column.

Just a thought.

Best regards,

TS

Thierry R. Sornasse

Highlighted

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

Created:
Aug 24, 2018 3:13 AM
| Last Modified: Aug 24, 2018 4:03 AM
(5762 views)
| Posted in reply to message from Thierry_S 08-23-2018

Generally, it's a bad idea to have data of different levels of granularity held in the same table. Additionally (like other statistical software) JMP requires data in a column to be of the same data type, modeling type and format.

But, so long as you don't mind a proportion rather than a percentage, if you want to do this you could use JSL:

```
NamesDefaultToHere(1);
// (1) Make some data
nr = 10; // Number of rows
nc = 5; // Number of columns
dt = NewTable("Data");
for (c = 1, c<=nc, c++, dt << NewColumn("Col "||Char(c), Formula(RandomINteger(-10, 10))));
dt << addRows(nr);
dt << runFormulas;
for (c = 1, c<=nc, c++, Column(dt, c) << deleteFormula);
// (2) Insert a new first row
dt << addRows(1, atStart);
// (3) Populate values of first row
for (c=1, c<=NCols(dt), c++,
vals = Column(dt, c) << getValues;
vals = vals[2::NRows(dt)];
Column(dt, c)[1] = NRows(Loc(vals < 0))/(NRows(dt)-1);
);
```

Highlighted
##

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

Re: How do I insert a row at the top of the table and compute the percentage of negative Numbers for

Thank you very much! That's what's needed.See the figure of VBA cycle.

Highlighted
##

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

Re: How do I insert a row at the top of the table and compute the percentage of negative Numbers for

Transpose might be a good idea, especially for labeling the percentages.

You might still want to work with untransposed data, particularly if the number of rows is not always the same.

You can insert a row above, like this

`dt<<addrows(1,at start);`

and you can fill it in with the percentages. But after you do that the columns won't be useful with the JMP analysis platforms that expect the data in a column to all be the same kind of data.

To give a better answer, we need to know what the next step is: is this the final presentation (tabulate, perhaps), or is there more analysis planned for the table and the percentages (another table might help)? Will the data be updated (maybe use column formulas)?

Craige

Highlighted
##

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

Re: How do I insert a row at the top of the table and compute the percentage of negative Numbers for

Different results are obtained by using the JMP model operation, and further processing of these results is not available for direct use by the JMP module.I always have to go to VBA and do my calculations.Lincoln That went well.

Article Labels

There are no labels assigned to this post.