topic Re: JMP equivalent of Excel's SUMPRODUCT function in Discussions

JMP equivalent of Excel's SUMPRODUCT function

BHarris — Tue, 05 Mar 2024 23:22:44 GMT

Is there a JMP equivalent of Excel's SUMPRODUCT function?

E.g., suppose I have a table like this:

Row	Set	Run	Probability	Value	Weighted Value
1	A	1	0.2	7.5
2	A	2	0.8	14.2
3	B	1	0.35	18.1
4	B	2	0.65	25.9

... I would like to add a function to the "Weighted Value" column that would populate Rows 1 and 2 with (0.2*7.5+0.8*14.2), and Rows 3 and 4 with (0.35*18.1+0.65*25.9).

I imagine it would look something like: SUMPRODUCT( { :Probability, :Value }, :Set ) ... where :Probability and :Value are the columns to be multiplied, then added, grouping on :Set. But obviously that's not a function in JMP -- is there something similar?

Re: JMP equivalent of Excel's SUMPRODUCT function

txnelson — Wed, 06 Mar 2024 01:15:55 GMT

Your guess was close.

Col Sum( :Probability * :Value, :Set )

Re: JMP equivalent of Excel's SUMPRODUCT function

Craige_Hales — Wed, 06 Mar 2024 16:20:05 GMT

@shannon_conners, @jules - the doc and scripting index should capture some of these excel translations. This one should have an explanation like

you can use any expression for the first parameter. For example, you can use 1 to count how many by-values, or a product of two columns. Or just a single column. Whatever it is, it will be added up until the by-value changes. The excel SumProduct function becomes ...

By including the relevant words, people can look it up. The doc was not clear that the first parameter did not need to be a simple column name (five examples, all use :height for the first parameter).