# How to sum 3 columns and divide by 100 in excel through Linq Query

Hi
I have excel data where i want to sum column A, B, C/100 in Total column in excel
here i want to sum the 3 columns and divide by 100

can any one help me how can we achieve this by Linq Query
i want to done this in one line of code in Linq.
is it possible ?

iam attaching excel sheet
Tax Data.xlsx (8.0 KB)

dt.AsEnumerable().ToList().ForEach(Sub(row) row(“Total”) = If(IsNumeric(row(“A”).ToString.Trim) AndAlso IsNumeric(row(“B”).ToString.Trim) AndAlso IsNumeric(row(“C”).ToString.Trim), Convert.ToDouble(row(“A”)) + Convert.ToDouble(row(“B”)) + (Convert.ToDouble(row(“C”)) / 100), 0)))

here iam getting error as end of statement expexcted can anyone rectify this statement

Its always best to keep it simple, and not complicate the solution,

this can easily be done by using an excel formula and using the auto fill function see below:

Hi

Input:

Output:

Workflow:

Code:

``````(From row In dt.AsEnumerable
Let a = CInt(row("Amoun1"))+CInt(row("Amount2"))+CInt(row("Amount3"))
``````

Xaml
UiPath.zip (1.5 KB)

Thanks,

@T_Y_Raju
Try this :

dt.AsEnumerable().ToList().ForEach(row =>
{
if (double.TryParse(row[“A”].ToString().Trim(), out double a) &&
double.TryParse(row[“B”].ToString().Trim(), out double b) &&
double.TryParse(row[“C”].ToString().Trim(), out double c))
{
row[“Total”] = a + b + (c / 100.0);
}
else
{
row[“Total”] = 0;
}
});

Thanks

let me try and check

let me try and check

here i want to divide the sum by 100 as well

@T_Y_Raju

For your case you can use expression directly

`dt.Columns("Total").Expression = "([Amoun1] + [Amount2] + [Amount3])/100"`

cheers

iam getting error as end of expression expected in value to save
iam taking assgin activity here

what should be the data type for assign

@T_Y_Raju

left of equals to be used on left and right to be used on right in assign

cheers

this expression will automatically write data to excel

@T_Y_Raju

This expression will give totals in datatable

You can use write range to write back

Cheers

ok here i already have data table dt variable and iam creating a column Total by suing add data column activity and writing back the data in write range and iam giving data table name as dt

and after that iam using the assign expression to calculate the total amount of the 3 columns
so when i write back data again in write range i have to use the old variable dt to write back or create new datatable variable Please clarify

will the old variable dt holds the data of sum of the columns?

@T_Y_Raju

If you are using same above expression then write dt back to excel you dont need to create any other table

Data will be populated into dt only

Cheers