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
- :
- Can this two-step formula be calculated in one step?

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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Dec 13, 2018 11:30 PM
(2821 views)

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

Big Class.jmp, for example

Increase column :B =: weight /: height

Increase the number of columns :C to count the number of columns :B starting from the same row up to >1.9 in the first 5 rows, and calculate the range of 6 rows at a time.

Is there a formula that can do this calculation quickly?

I tried to write the formula in the lower left corner of the graph, but it's not correct.

Thanks!

3 ACCEPTED SOLUTIONS

Accepted Solutions

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

This matches for me.

```
x = Index( Row() - 5, Row() );
// you have to do element wise divide :/
Sum( :weight[x] :/ :height[x] > 1.9 );
```

Vince Faller - Predictum

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

Try this formula:

`Sum(:weight[Row() - 4 :: Row()] :* (:height[Row() - 4 :: Row()] > 60));`

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

And jmp's [Index(Row()-1,Row()-1)] like excel's INDIRECT()

12 REPLIES 12

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

This matches for me.

```
x = Index( Row() - 5, Row() );
// you have to do element wise divide :/
Sum( :weight[x] :/ :height[x] > 1.9 );
```

Vince Faller - Predictum

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

Re: Can this two-step formula be calculated in one step?

Thank for vince_faller's help!

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

Re: Can this two-step formula be calculated in one step?

@vince_faller wrote:This matches for me.

`x = Index( Row() - 5, Row() ); // you have to do element wise divide :/ Sum( :weight[x] :/ :height[x] > 1.9 );`

Taking file Big Class.jmp as an example, I would like to ask another question, which is how to replace the original two-step formula with one-step calculation:

I'm going to simplify the data first: I'm going to add 100 that rows are 8 multiples of the "height" (in the red box).

Increasing "tem" = = if (: weight > : height, 1, 0)

Increase the column 2 "ongoing", computation requirement is: how to "tem" column no 0, will "tem" list of all the various numerical accumulated;If "tem" column ==0, "ongoing" column this line has a value of 0, the following from the "tem" columns from the line began to cumulative sum.Thanks for your time.

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

Re: Can this two-step formula be calculated in one step?

I tried to write the formula for tt column, but the parameter c cannot be updated, so the result is incorrect.

Please instruct me.Thanks!

Please instruct me.Thanks!

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

Re: Can this two-step formula be calculated in one step?

correct

c=1;

If( :weight < :height,

c = Row();

:tt = 0; ,

Sum( :weight[Index( c, Row() )] :/ :height[Index( c, Row() )] > 1 );

);

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

Re: Can this two-step formula be calculated in one step?

I see :

if(row()==1,

c=1;

Sum( :weight[Index( c, Row() )] :/ :height[Index( c, Row() )] > 1 ),

If( :weight < :height,

c = Row();

:tt = 0;

,

Sum( :weight[Index( c, Row() )] :/ :height[Index( c, Row() )] > 1 );

)

);

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

Re: Can this two-step formula be calculated in one step?

Continue to ask similar questions and find no answer in the community:

figure out the total : weight of :height> 60 of the from this row to the top 4 rows.

thank you!

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

Try this formula:

`Sum(:weight[Row() - 4 :: Row()] :* (:height[Row() - 4 :: Row()] > 60));`

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

Re: Can this two-step formula be calculated in one step?

I see.

That like excel's =SUMPRODUCT(()*())

Thank ms!

That like excel's =SUMPRODUCT(()*())

Thank ms!