BookmarkSubscribe
Choose Language Hide Translation Bar
robertwf
Community Trekker

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
0 Kudos
4 REPLIES 4
dbailey
Community Trekker

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))
0 Kudos
robertwf
Community Trekker

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.
0 Kudos
pmroz
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;">




 

0 Kudos
robertwf
Community Trekker

Re: Col Sum function question

Worked perfectly!

Thank you PMroz
0 Kudos