Subscribe Bookmark RSS Feed

Col Sum function question

robertwf

Community Trekker

Joined:

Jun 23, 2011

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
dbailey

Community Trekker

Joined:

Jun 23, 2011

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))
robertwf

Community Trekker

Joined:

Jun 23, 2011

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.
pmroz

Super User

Joined:

Jun 23, 2011


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;">




 

robertwf

Community Trekker

Joined:

Jun 23, 2011

Worked perfectly!

Thank you PMroz