Column Custom 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 'Id' where the 'Id' is nearest to 1e-6; and then Vth = Vg(i). It should be grouped by Mod and Device columns as well. That is, each unique combination of Mod and Device will have one Vth value.

Id	Vg	Mod	Device	Vth
2.00E-06	3	1A	X1
2.00E-07	2.8	1A	X1
2.00E-08	2.6	1A	X1
2.00E-09	2.4	1A	X1
2.00E-10	2.2	1A	X1
2.00E-12	2	1A	X1
3.00E-06	3	1B	X1
3.00E-07	2.8	1B	X1
3.00E-08	2.6	1B	X1
3.00E-09	2.4	1B	X1
3.00E-10	2.2	1B	X1
3.00E-12	2	1B	X1
4.00E-06	3	1A	Y1
4.00E-07	2.8	1A	Y1
4.00E-08	2.6	1A	Y1
4.00E-09	2.4	1A	Y1
4.00E-10	2.2	1A	Y1
4.00E-12	2	1A	Y1
5.00E-06	3	1B	Y1
5.00E-07	2.8	1B	Y1
5.00E-08	2.6	1B	Y1
5.00E-09	2.4	1B	Y1
5.00E-10	2.2	1B	Y1
5.00E-12	2	1B	Y1

[#2]

Here is a little script that create 2 new columns that gives you the results you asked for.  The image below has had the interim column deleted since it is only a working column.  

 

Names default to Here(1);

// Create the sample data table
dt=New Table( "Example",
	Add Rows( 24 ),
	New Column( "ID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0.000002, 0.0000002, 0.00000002, 0.000000002, 0.0000000002,
			0.000000000002, 0.000003, 0.0000003, 0.00000003, 0.000000003,
			0.0000000003, 0.000000000003, 0.000004, 0.0000004, 0.00000004,
			0.000000004, 0.0000000004, 0.000000000004, 0.000005, 0.0000005,
			0.00000005, 0.000000005, 0.0000000005, 0.000000000005]
		)
	),
	New Column( "VG",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4,
			2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2]
		)
	),
	New Column( "Mod",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B",
			"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B"}
		)
	),
	New Column( "Device",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1",
			"Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1"}
		)
	)
);

// Find the index
dt << New Column("Row", formula(
theMin = Col Minimum( Abs( 0.000001 - :ID ), :Mod, :Device );
theValue = .;
If( Abs( 0.000001 - :ID[Row()] ) == theMin,
	theValue = Row()
);));

// Set the Vth
dt<<New Column("Vth", formula(
theRow = :VG[Col Max( :Row, :Mod, :Device )]));

// Clean up
dt:Vth << delete formula;
dt<< delete columns("Row");

[#3]

Thanks Jim. It works. How can I modify this to find the Vth which corresponds to exactly 1e-6 of ID by interpolation. I.e. finding an index before and after 1e-6 of ID and then interpolate.

index=nearest to 1e-6 (greater than 1e-6)

Interpolate( x, [VG(index) VG(index-1)  ], [log10(ID(index))  log10(ID(index-1))] );

[#4]

Here are the columns to generate what you are asking for.  I took your interpolation formula as is, and implemented it.  You may need to changes some things up.

Names default to Here(1);

// Create the sample data table
dt=New Table( "Example",
	Add Rows( 24 ),
	New Column( "ID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[0.000002, 0.0000002, 0.00000002, 0.000000002, 0.0000000002,
			0.000000000002, 0.000003, 0.0000003, 0.00000003, 0.000000003,
			0.0000000003, 0.000000000003, 0.000004, 0.0000004, 0.00000004,
			0.000000004, 0.0000000004, 0.000000000004, 0.000005, 0.0000005,
			0.00000005, 0.000000005, 0.0000000005, 0.000000000005]
		)
	),
	New Column( "VG",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values(
			[3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2, 3, 2.8, 2.6, 2.4,
			2.2, 2, 3, 2.8, 2.6, 2.4, 2.2, 2]
		)
	),
	New Column( "Mod",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B",
			"1A", "1A", "1A", "1A", "1A", "1A", "1B", "1B", "1B", "1B", "1B", "1B"}
		)
	),
	New Column( "Device",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1", "X1",
			"Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1", "Y1"}
		)
	)
);

// Find the index
dt << New Column("Row", formula(
theMin = Col Minimum( Abs( 0.000001 - :ID ), :Mod, :Device );
theValue = .;
If( Abs( 0.000001 - :ID[Row()] ) == theMin,
	show(.000001 - :ID[Row()]);
	theValue = Row()
);));

dt<<New Column("Interpolation Row", formula(
theRow = Col Max( :Row, :Mod, :Device );
iRow = .;
If( Row() == theRow,
	If( .000001 - :ID[theRow] > 0,
		iRow = theRow - 1,
		.000001 - :ID[theRow] < 0,
		iRow = theRow +1,
		iRow = theRow
	);
);
));
iRow;

// Set the Vth
dt<<New Column("Vth", formula(
theRow = :VG[Col Max( :Row, :Mod, :Device )]));

dt<<New Column("Exact Vth", formula(
	theRow = Col Max( :Row, :Mod, :Device );
	a=matrix(:VG[:Row[theRow]]) || matrix(:VG[:Interpolation Row[theRow]]);
	b=matrix(log10(:ID[:Row[theRow]])) || matrix(log10(:ID[:Interpolation Row[theRow]]));
	theValue=interpolate(.0000001, a, b);
	theValue;
));

// Clean up
dt:Vth << delete formula;
dt:Exact Vth << delete formula;
//dt<< delete columns("Row");
//dt<< delete columns("Interpolation Row");