Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Column Custom Formula

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Column Custom Formula

Jul 22, 2020 4:09 PM
(286 views)

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 |

3 REPLIES 3

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Column Custom Formula

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

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Column Custom Formula

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Column Custom Formula

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

Jim