cancel
Showing results for
Show  only  | Search instead for
Did you mean:
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
Discovery is online this week, April 16 and 18. Join us for these exciting interactive sessions.
Choose Language Hide Translation Bar
Level II

## How can you calculate the product of non-empty rows up to the current row?

I'm trying to write a column formula that will calculate the product of all of the (non-empty) rows up to the current row.

I've attached an example data table (& screenshot).

I am trying to get the "Product" column to be the product of all of the non-empty rows from the "Value" column. I show the manually calculated result and math in the "Desired Result" & "Desired Result Explanation" to the right.

A couple data set issues I tried to highlight in the example data table.

1. The number of rows per "Group" will vary between groups.

2. The "Group" will always be together and in order. All group 1 will be together. Group1 will always come before group 2.

3. The Value column will only have one non-empty row per group. It will always be the first row of that group.

I think this might require a "loop" or "for" type of formula, but have limited experience with building these up.

Even if you don't have a solution I appreciate any thoughts or links to guides or other discussions you think might lead me to a solution.

Previous Attempts:
I thought that txnelson's response to this question (https://community.jmp.com/t5/Discussions/How-to-multiply-all-elements-in-a-list/td-p/667633) might work but haven't gotten it to give the result I'm looking for.

Thank you for your time and help!

JMP Version: 17.2.0, Windows 10

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: How can you calculate the product of non-empty rows up to the current row?

One option using the knowledge that the value is always found from the first row for each group

``````If(Row() == 1,
val = 1;
);
If(Row() == Col Min(Row(), :Group),
val = val * :Value;
,
.;
);
``````

Edit:

And here is other checking if :Value is missing or not

``````If(Row() == 1,
val = 1;
);
If(!IsMissing(:Value),
val = val * :Value;
,
.
);``````

You should also be able to replace first If with As Constant

``````As Constant(val = 1);
If(!IsMissing(:Value),
val = val * :Value;
,
.
);``````
-Jarmo
2 REPLIES 2
Super User

## Re: How can you calculate the product of non-empty rows up to the current row?

One option using the knowledge that the value is always found from the first row for each group

``````If(Row() == 1,
val = 1;
);
If(Row() == Col Min(Row(), :Group),
val = val * :Value;
,
.;
);
``````

Edit:

And here is other checking if :Value is missing or not

``````If(Row() == 1,
val = 1;
);
If(!IsMissing(:Value),
val = val * :Value;
,
.
);``````

You should also be able to replace first If with As Constant

``````As Constant(val = 1);
If(!IsMissing(:Value),
val = val * :Value;
,
.
);``````
-Jarmo
Level II

## Re: How can you calculate the product of non-empty rows up to the current row?

Thank you Jarmo, worked perfectly!