Excel/Mathematical operations/Expression on DataTable Rows

Question

2.85/5 (5 votes)

See more:

ckquote class="FQ">

Quote:

</div>pre></blockquote>
Expression for Sigma and CpK
Input
DataTable1 --- table1

|Parameter Name| Lower |Upper   |point90      |point10     |point50    |
  Diff_IC[1]   |  1.5  |2.5     |0.1471839    |0.137499005 |0.141139999 |
  Prog_IC[2]   |-10    |10000000|0.148519993  |0.137592003 |0.141236007 |
  Nam_IC[3]    |-64    |64      |0.157493005  |0.117183459 |0.141788006|
  ADCI_N[1 ]   |-0.8   |-0.1    |0.138519993  | 0.117592003|0.142398   |


Output
|Parameter Name| Lower |Upper    |point 90     |point10    |point50  |Sigma       |CpK |
  Diff_IC[1]   |  1.5  |2.5      |0.147183999  |0.137499005|0.141139999 |            |    |
  Prog_IC[2]   |-10    |10000000 |0.148519993  |0.137592003 |0.141236007|            |    |
  Nam_IC[3]    |-64    |64       |0.157493005  |0.117183459|0.141788006|             |    |
  ADCI_N[1 ]   |-0.8   | -0.1    |0.138519993  |0.117592003 |0.142398   |            |    |


Formula:
Sigma = (P90 point-P10 point)/2.56
Cpk =  Minimum|[  (P50 point - Lower) / 3 (sigma), (upper - P50 pint)  / 3 (sigma)]|

Posted 11-Mar-14 0:07am

FarhanShariff

Updated 12-Mar-14 0:24am

v12

Add a Solution

Comments

agent_kruger 11-Mar-14 6:27am

sir, i supposed instead of posting question you posted an answer. :laugh

FarhanShariff 11-Mar-14 6:31am

No its a question and how the answer should look like yeah with values filled for Sigma and CpK of Course :D

Kenneth Haugland 11-Mar-14 7:10am

So, you want excel functions on a DataGridView?

phil.o 11-Mar-14 8:55am

It's not a question. A question is something formulated in a human-readable language. It has to have a meaning, and expose some kind of problem/issue. Usually questions are punctued with a question mark.
Yours is just a repost.

FarhanShariff 11-Mar-14 8:00am

Yeah Exactly on DataTable

Ganesh Rana <> 11-Mar-14 8:06am

in this case where we put last row result??

FarhanShariff 11-Mar-14 8:07am

Last row result ? what do you mean ?

Ganesh Rana <> 11-Mar-14 8:44am

you want math operation on next row ??
else i can not get it what u want ??

FarhanShariff 11-Mar-14 8:48am

No Ganesh Sigma and CpK have to be calculated

Ganesh Rana <> 11-Mar-14 8:53am

ok.
But when math operation calculated for zero index row then u want to put it to zero index or next row(one index).

FarhanShariff 11-Mar-14 8:57am

0 index. For instance Sigma for Diff_IC[1] should be in same row

2 solutions

Solution 1

C#

DataTable dtDemo =  new DataTable();
dtDemo.columns["Sigma"].readonly = false;
for(int i =0 ; i < dtDemo.rows.count; i++)
{
	dtColumns.rows[i]["Sigma"] = Convert.ToDouble(dtColumns.rows[i]["Upper"]) - Convert.ToDouble(dtColumns.rows[i]["Lower"]);
// here i just given one demo u need to change as per your requirement
}

Posted 11-Mar-14 3:10am

Ganesh Rana <>

Comments

FarhanShariff 11-Mar-14 9:21am

thank you...Ganesh

Ganesh Rana <> 11-Mar-14 9:24am

int i = 5 ;
int j = 15;
// now to find min??
int min = i > j ? j : i;

FarhanShariff 11-Mar-14 11:33am

I get Error Object cannot be cast from DBNull to other types.

dtColumns.rows[i]["Sigma"] = Convert.ToDouble(dtColumns.rows[i]["Upper"]) - Convert.ToDouble(dtColumns.rows[i]["Lower"]);

FarhanShariff 11-Mar-14 9:57am

I want to add columns and set their expression properties is it possible ?

Ganesh Rana <> 11-Mar-14 10:05am

yes , it is possible ..
take care of providing data type of column.
// provide float

Ganesh Rana <> 11-Mar-14 10:08am

dtDemo.Columns["Sigma"].Expression = "Upper - Lower";

// here dtDemo data table must have column sigma as float

FarhanShariff 11-Mar-14 10:15am

DataColumn sigma = table1.Columns.Add("Sigma", typeof(float));
DataColumn cpK = table1.Columns.Add("CpK", typeof(float));

// set their expression properties
sigma.Expression = "point90 - point10 /2.56";

cpK.Expression = "IIF(([point50] - LSL) < (USL - [point50]), [point50] - LSL, USL - [point50])/3/Sigma"; //Error Cannot perform '-' operation on System.Double and System.String.

Ganesh Rana <> 12-Mar-14 1:49am

in that case any one of column have string datatype so, that can not perform math operation.

if possible don't use any brackets.
like (try this)
dtDemo.Columns["cpk"].Expression = "IIF(point50 - LSL < USL - point50,point50 - LSL,USL - point50/3/Sigma)";

FarhanShariff 12-Mar-14 6:35am

I want to put Minimum Value of (cpKL,cpKR) into cpK How to write the expression for cpK

sigma.Expression = "(Convert(point90,'System.Double')-Convert(point10,'System.Double'))/2.56";

cpKL.Expression = "(Convert(point50,'System.Double')- Convert(LSL,'System.Double'))/(3.0*sigma)";

cpKR.Expression = "(Convert(USL,'System.Double') - Convert(point50,'System.Double'))/(3.0*sigma)";

// The expression contains undefined function call Math.Min().
//cpK.Expression = "Math.Min((Convert(cpKL,'System.Double'),(Convert(cpKR,'System.Double'))"

Ganesh Rana <> 12-Mar-14 7:26am

cpk.Expression = "IIF(cpKL > cpKR,'cpKR','cpKL')";

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

FarhanShariff · Accepted Answer · 2014-03-11T22:54:00

DataColumn sigma = table1.Columns.Add("Sigma", typeof(double));
DataColumn cpKL = table1.Columns.Add("CpKL", typeof(double));
DataColumn cpKR = table1.Columns.Add("CpKR", typeof(double));
DataColumn cpK = table1.Columns.Add("CpK", typeof(double));


sigma.Expression="(Convert(point90,'System.Double')-Convert(point10,'System.Double'))/2.56";
cpKL.Expression="(Convert(point50,'System.Double')-Convert(LSL,'System.Double'))/(3.0*sigma)";
cpKR.Expression="(Convert(USL,'System.Double')-Convert(point50,'System.Double'))/(3.0*sigma)";
cpK.Expression = "IIF(cpKL < cpKR, cpKL, cpKR)";