# Calculations on DataColumns in a DataTable

• ### Question

• I have a data table with a column . I need to do calculations on column1 data and add the result to the column 2. I need to subtract the current row and previous row of column1 and divide by the previous row value.

`column2[i]=(column1.Row[i]-column1.Row[i-1])/column1.Row[i-1] `

I am getting the data using ADO.net logic in the class: DataLayer.

```                    dLayer.dt.Columns.Add("Column2")

foreach(DataRow row in dataLayer.dt.Rows )
{
//             dLayer.dt.Columns["column2"]

}```

• Edited by Saturday, August 29, 2015 1:43 PM
Saturday, August 29, 2015 12:37 PM

• You could convert the values in columns i and i - 1 to the appropriate data type (int, double, etc.) and use a for loop, e.g.:

```            dLayer.dt.Columns.Add("Column2");

dLayer.dt.Rows[0]["column2"] = 0;
for (int i = 1; i < dLayer.dt.Rows.Count; ++i)
{
int a = Convert.ToInt32(dLayer.dt.Rows[i]["column1"]);
int b = Convert.ToInt32(dLayer.dt.Rows[i - 1]["column1"]);
int c = a - b / b;
dLayer.dt.Rows[i]["column2"] = c;
}```

Hope that helps.

• Marked as answer by Saturday, August 29, 2015 1:55 PM
• Edited by Saturday, August 29, 2015 1:58 PM code change
Saturday, August 29, 2015 1:30 PM

### All replies

• You could convert the values in columns i and i - 1 to the appropriate data type (int, double, etc.) and use a for loop, e.g.:

```            dLayer.dt.Columns.Add("Column2");

dLayer.dt.Rows[0]["column2"] = 0;
for (int i = 1; i < dLayer.dt.Rows.Count; ++i)
{
int a = Convert.ToInt32(dLayer.dt.Rows[i]["column1"]);
int b = Convert.ToInt32(dLayer.dt.Rows[i - 1]["column1"]);
int c = a - b / b;
dLayer.dt.Rows[i]["column2"] = c;
}```

Hope that helps.

• Marked as answer by Saturday, August 29, 2015 1:55 PM
• Edited by Saturday, August 29, 2015 1:58 PM code change
Saturday, August 29, 2015 1:30 PM
• A datacolumn in a datatable can be calculated using an expression:

https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

However, that expression has to act on columns in the current row.

You could use a combination of an expression and code. Add a column which has the previous row's column1 value in it.

Or you could just loop and do the calculation.

Something like:

```double LastOne = 0.0;

foreach(DataRow row in dataLayer.dt.Rows )
{
if (LastOne > 0)
row["column2"] = ( row["column1"] - LastOne ) / LastOne;
LastOnce = row["column1"];
}```

Saturday, August 29, 2015 1:34 PM
• See the edited post
Saturday, August 29, 2015 1:44 PM
• See the edited post

You too :)

The (slightly updated) code sample I provided iterates through all but the first row in the DataTable and sets the value of the second column to the value of column1 of the current row minus the value of column1 divided by the value of column1 of the previous row. Row number 0 has no previous row so you need to handle this one specifically. In my sample code I have just set its column2 to 0.

The provided code code sample should get you the idea.

Note that you should use Convert.ToDouble or Convert.ToDecimal instead of Convert.ToInt32 if your columns contains decimal values.

Hope that helps.