Choose Language Hide Translation Bar
Highlighted
Level I

## Col Sum function question

I'm building a column formula in the formula editor and have run into a problem with the Col Sum function.

My dataset is structured as follows:

Claim a b
3154 1.5 10000
844 .5 1000000
7311 1.5 1000000
22777 .5 10000
. . .

and I'd like to sum the values according to the function:

Sum over i=1 to n {log(claim_i/b)*[(b/claim_i)^a + 1]^(-1)}.

The claim values are to be summed for all the rows in my dataset, hence a Col Sum function seems the natural choice. However, I don't want to sum the a and b variables by row - I want just the row value of each a and b to be included in the formula.

Normally I would use algebraic manipulations to bring the a and b constants outside of the summation. However, given the complexity of this formula, this isn't an option.

Is it possible to exclude column variables from the Col Sum function?

Many thanks,

Robert
4 REPLIES 4
Highlighted
Level I

## Re: Col Sum function question

So does that make your formula is really

log(claim_i/b_i)*[(b_i/claim_i)^a_i + 1]^(-1)

if so, then could you use

sum(log(claim/b)*[(b/claim)^a + 1]^(-1))
Highlighted
Level I

## Re: Col Sum function question

Right, except I don't want to sum the a's and b's over every row.

For example, the summation for the first row (where a=1.5 and b=10,000)
would be:

sum1=
log(3154/10000)*[(10000/3154)^1.5 + 1]^(-1) +
log(844/10000)*[(10000/844)^1.5 + 1]^(-1) +
log(7311/10000)*[(10000/7311)^1.5 + 1]^(-1) +
log(22777/10000)*[(10000/22777)^1.5 + 1]^(-1) +. . .

and the summation for the second row (where a=.5 and b=1,000,000)
would be:

sum2=
log(3154/1000000)*[(1000000/3154)^.5 + 1]^(-1) +
log(844/1000000)*[(1000000/844)^.5 + 1]^(-1) +
log(7311/1000000)*[(1000000/7311)^.5 + 1]^(-1) +
log(22777/1000000)*[(1000000/22777)^.5 + 1]^(-1) +. . .

I'm not sure JMP can perform this kind of sum.
Highlighted
Super User

## Re: Col Sum function question

style="font-size: 10pt; font-family: "Courier New"; color: black;">

Your
formula would be something like the following:

style="font-size: 10pt; font-family: "Courier New"; color: black;">dt

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">current
data table

style="font-size: 10pt; font-family: "Courier New"; color: black;">()

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">mysum

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: teal;">0

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">for

style="font-size: 10pt; font-family: "Courier New"; color: black;">
(i

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: teal;">1

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">
i

style="font-size: 10pt; font-family: "Courier New"; color: navy;"><=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">nrows

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">dt)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">
i

style="font-size: 10pt; font-family: "Courier New"; color: navy;">++,

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">
claim_i

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">column

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">dt

style="font-size: 10pt; font-family: "Courier New"; color: navy;">,

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: purple;">"Claim"

style="font-size: 10pt; font-family: "Courier New"; color: black;">)[

style="font-size: 10pt; font-family: "Courier New"; color: black;">i]

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">
style="">
mysum

style="font-size: 10pt; font-family: "Courier New"; color: navy;">=

style="font-size: 10pt; font-family: "Courier New"; color: black;">
mysum

style="font-size: 10pt; font-family: "Courier New"; color: navy;">+

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: rgb(0, 0, 221);">log

style="font-size: 10pt; font-family: "Courier New"; color: black;">(

style="font-size: 10pt; font-family: "Courier New"; color: black;">claim_i

style="font-size: 10pt; font-family: "Courier New"; color: navy;">/:

style="font-size: 10pt; font-family: "Courier New"; color: black;">b)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">*

style="font-size: 10pt; font-family: "Courier New"; color: black;">
((

style="font-size: 10pt; font-family: "Courier New"; color: navy;">:

style="font-size: 10pt; font-family: "Courier New"; color: black;">b

style="font-size: 10pt; font-family: "Courier New"; color: navy;">/

style="font-size: 10pt; font-family: "Courier New"; color: black;">claim_i)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">^:

style="font-size: 10pt; font-family: "Courier New"; color: black;">a

style="font-size: 10pt; font-family: "Courier New"; color: navy;">+

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: teal;">1

style="font-size: 10pt; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: navy;">^

style="font-size: 10pt; font-family: "Courier New"; color: black;">
(

style="font-size: 10pt; font-family: "Courier New"; color: navy;">-

style="font-size: 10pt; font-family: "Courier New"; color: teal;">1

style="font-size: 10pt; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">)

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">

style="font-size: 10pt; font-family: "Courier New"; color: black;">mysum

style="font-size: 10pt; font-family: "Courier New"; color: navy;">;

style="font-size: 10pt; font-family: "Courier New"; color: black;">

Highlighted
Level I

## Re: Col Sum function question

Worked perfectly!

Thank you PMroz
Article Labels

There are no labels assigned to this post.