topic Re: Writing Excel formula in JSL script (Formula in attached excel file) in Discussions

Writing Excel formula in JSL script (Formula in attached excel file)

LearningJMP — Sat, 10 Jun 2023 23:53:17 GMT

Hi,

I am trying to replicate formulas that are currently in the attached excel sheet.

Where, DSA_Step_Size(dB) = ABS(of delta of :RESULT rows when ATT1 values are decreasing for the same :FREQUENCY value).

The formula is in excel sheet if my explanation is confusing.

DSA Range(dB) = ABS(of delta of :RESULT rows when ATT1=63 and ATT1=0 for each :FREQUENCY).

I am trying to use for loops in the formula.. so far not getting the result I want:

n = 63;
For( i = 1, i <= n, i++,
:RESULT[i] - :RESULT[i + 3]
);

Re: Writing Excel formula in JSL script (Formula in attached excel file)

vince_faller — Thu, 18 Aug 2022 23:02:33 GMT

if(row() <= 63, :RESULT[Row()] - :RESULT[Row() + 3])

Re: Writing Excel formula in JSL script (Formula in attached excel file)

txnelson — Fri, 19 Aug 2022 01:17:42 GMT

@vince_faller provides a solution that works for your data table in the form after the table has been sorted. I am not sure what it takes to order your data.

Below, is a formula that was developed using the imported Excel file and making no modification to the file once it is imported into JMP.

If( Row() == 1 | :ATT1 != Lag( :ATT1 ),
	currATT1 = :ATT1;
	currFreq = :FREQUENCY;
	currRow = Row();
	If( :ATT1 == 0,
		theRow = 0,
		theRow = (Current Data Table() << get rows where(
			Row() > currRow & :ATT1 == currATT1 - 1 & :FREQUENCY == currFreq
		))[1] - Row()
	);
);
:RESULT - :RESULT[Row() + theRow];

Each time the value of ATT! changes, the formula looks ahead to find the next row where the FREQUENCY is the same as the current FREQUENCY and the ATT! is 1 less than the current value of ATT1 and finds the number of rows between the current row, and the look ahead row. It uses that offset value from that point on, until the value of ATT1 changes again.

Attached is the imported Excel file I used.