nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

3308_en-US

liliconcom

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

Re: Col Sum function question

Worked perfectly!

Thank you PMroz

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

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.

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

Comments

There are no comments yet