Custom Column formula

[#1]

I would like to add a new column 'Vth' in my table with the following calculation:-

First I need to find the index i of column 'IDS' where the 'IDS' is nearest to 1e-9 (or Log10[IDS] = -9); and then Vth = VGS(i). It should be grouped by Site, Measurement, SWP, Wafer columns as well. That is, each unique combination of Site, Measurement, SWP, Wafer will have one Vth value.

If the IDS is not exactly 1e-9. Please do interpolate in this fashion. That is; Log10[IDS] vs. VGS.

Exact VTH = Interpolate( Log10( 1e-9 ), [Log10[IDS](i)) Log10[IDS](i-1)] , [VGS (i) VGS (i-1) ] );

Note the SWP column is sweep of VGS in forward (0) and reverse (1) direction; therefore the interpolation direction need to be taken care of in proper manner.

[#2]

please use this JMP file. Ignore the excel above.

Updated description below:-

I would like to add a new column 'Vth' in my table with the following calculation:-

First I need to find the index i of column 'IDS' where the 'IDS' is nearest to 1e-9 (or Log10[IDS] = -9); and then Vth = VGS(i). It should be grouped by Site, Measurement, SWP, Device, Wafer columns as well. That is, each unique combination of Site, Measurement, SWP, Wafer, Device will have one Vth value.

If the IDS is not exactly 1e-9. Please do interpolate in this fashion. That is; Log10[IDS] vs. VGS.

Exact VTH = Interpolate( Log10( 1e-9 ), [Log10[IDS](i)) Log10[IDS](i-1)] , [VGS (i) VGS (i-1) ] );

Note the SWP column is sweep of VGS in forward (0) and reverse (1) direction; therefore the interpolation direction need to be taken care of in proper manner.

[#3]

Was anyone able to look at it?

[#4]

Hi,
It turns out that it is a bit more challenging than expected to get a column formula to do what you ask.
I hope others will have more luck than I have had so far.
Best,
TS

[#5]

Hi,

Here is how far I have gone: I can find the ID of the IDS closest to 1e-9 by Wafer, Site, Measurement, Device, and Wafer and then return the corresponding VGS otherwise returns 0. However, the interpolation is, as I mentioned earlier, more challenging.

If(
	Abs( :Name( "Log10[IDS]" ) + 9 ) == Col Minimum(
		Abs( :Name( "Log10[IDS]" ) + 9 ),
		:Site,
		:Measurement,
		:Device,
		:Wafer
	),
	:VGS,
	0
);

Best,

TS

[#6]

Hi, pankajsync!

Yes, I was able to look at it.

Finding the VGS value relating to the IDS value closest to 1e-9 is pretty trivial.  Set up a loss function column that takes the absolute value of log10(IDS)+9, and then identify the Col Minimum of that value by Site, Measurement, SWP, Device, Wafer.  The VGS corresponding to those loss value minimums will be your inexact Vth.

But none of the IDS values are exactly 1e-9, and some of the "closest" IDS values are quite distant from 1e-9, so please explain in more detail what procedure and result you intend to happen when you use the word "interpolate".  Do you mean a prediction of Vth if the IDS would be exactly 1e-9?  Using a linear model?  Does every unique Site, Measurement, SWP, Device, Wafer combination require this procedure irrespective of how far the IDS value is from 1e-9?

Perhaps, in addition to answering these questions, you could share what approaches you have unsuccessfully tried already?  Personally, I would consider writing a JSL script to do this because it seems like your application may benefit from some error checking and verifying initial data boundary conditions, but some of the denizens of this Discussion site are Formula Jedi that seriously impress me with their skills, and they might have a solution if you must have a formula.  If I anticipate your answers correctly, I don't think I will however.

Cheers,

Kevin

[#7]

Hi, Kevin!

I was thinking you can take this approach. 

Find VGS, logIDS, which is just above -9..-- Call it VGS_u, log(IDS)_u

Find VGS, logIDS, which is slightly below -9..-- Call it VGS_i, log(IDS)_i

calculate slope, m = abs(log(IDS)_u-log(IDS)_i)./abs( log(IDS)_u- log(IDS)_i)

Exact VTH = m*(abs(VGS_u-VGS_i)) + min(abs( log(IDS)_u), abs( log(IDS)_i) )

I really wanted custom formula for it even if it can be done through formulas in multiple columns.

Regards,

Pankaj

[#8]

i made some typos in below email. Below should be correct

Find VGS, logIDS, which is just above -9..-- Call it VGS_u, log(IDS)_u

Find VGS, logIDS, which is just below -9..-- Call it VGS_i, log(IDS)_i

calculate slope, m = abs(log(IDS)_u-log(IDS)_i)./abs( abs(VGS_u-VGS_i))

Exact VTH = VGS_i + 1/m*(9-abs(log(IDS)_i)))

[#9]

If it's possible to have it as a column formula it would be quite difficult.  Probably the best choice for you would be a table script or just a regular script.  The following seems to work well.

dt = Current Data Table();
summarized table = dt << Summary( Group( :Site, :Measurement, :SWP, :Device, :Wafer ) );
lookup = [=> ];
For Each Row(
	summarized table,
	If( !(lookup << Contains( :Site, Empty() )),
		lookup[:Site] = [=> ]
	);
	If( !(lookup[:Site] << Contains( :Measurement, Empty() )),
		lookup[:Site][:Measurement] = [=> ]
	);
	If( !(lookup[:Site][:Measurement] << Contains( :SWP, Empty() )),
		lookup[:Site][:Measurement][:SWP] = [=> ]
	);
	If( !(lookup[:Site][:Measurement][:SWP] << Contains( :Device, Empty() )),
		lookup[:Site][:Measurement][:SWP][:Device] = [=> ]
	);
	If( !(lookup[:Site][:Measurement][:SWP][:Device] << Contains( :Wafer, Empty() )),
		lookup[:Site][:Measurement][:SWP][:Device][:Wafer] = [=> ]
	);
	summarized table << Clear Select;
	summarized table << Select Rows( Row() );
	rows = dt << Get Selected Rows;
	vals = Column( dt, "Log10[IDS]" )[rows];
	test = {};
	For( i = 1, i <= N Items( rows ), i++,
		test[i] = Eval List( {Abs( 9 + vals[i] ), rows[i]} )
	);
	Sort List Into( test );
	lookup[:Site][:Measurement][:SWP][:Device][:Wafer] = Column( dt, "VGS" )[test[1][2]];
);
Close( summarized table, No Save );

Eval( Eval Expr(
dt << New Column( "LOOKUP_RESULT", "Numeric", Formula( lookup = Expr( lookup ); lookup[:site][:measurement][:swp][:device][:wafer] ) )
) )