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
- :
- JMP Formula

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 13, 2016 7:45 AM
(3748 views)

I have a quick question, does anyone know how you would approach using this Excel formula in the JMP Pro 12 formula platform to generate decile of equal distribution without using the cut point system? How do you get the INT component of the formula below in JMP? Assuming the total rows of your data table is 218086 and you want to have decile of 20,000 plus. This is what I use in Excel, but it be difficult when your data table has more than a 1 million rows than in this case Excel becomes less effective due to the 1 million rows limit.

=INT((ROW(B2)-1)/218086*10)+1

Jenkins Macedo

1 ACCEPTED SOLUTION

Accepted Solutions

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

Yes, of course it's 'Floor()' not 'Round()'. Apologies.

5 REPLIES

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

Jan 13, 2016 7:59 AM
(3489 views)
| Posted in reply to message from jenkins_macedo 01/13/2016 10:45 AM

To round a number to the nearest integer you can use 'Round(x, 0)', where x is your number or an expression that evaluates to a number.

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

Sorry to ask this @Ian, and how exactly would that formula be?

Jenkins Macedo

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

Jan 13, 2016 9:44 AM
(3489 views)
| Posted in reply to message from jenkins_macedo 01/13/2016 10:45 AM

I believe the direct translation of your formula from Excel to JMP would be:

Floor((Row()-1)/218086*10)+1;

The Floor() function returns the integer portion of the value, and the Row() function returns the row number the formula is being applied to

Jim

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

Yes, of course it's 'Floor()' not 'Round()'. Apologies.

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

Jan 21, 2016 11:36 AM
(3489 views)
| Posted in reply to message from jenkins_macedo 01/13/2016 10:45 AM

Thanks fellows. It worked well and I was able to ranked the model scores of customers of a data table with 1.2 million in decile of 114K

Jenkins Macedo