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

Help Activities
, ,
1

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)

2

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

3

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:

image
image570×643 17.2 KB

This is your output:
image

4

Hi

Input:
image

Output:
image

Workflow:
image

Code:

(From row In dt.AsEnumerable
Let a = CInt(row("Amoun1"))+CInt(row("Amount2"))+CInt(row("Amount3"))
Select dt.Clone.Rows.Add({row("Amoun1"),row("Amoun1"),row("Amoun1"),a})).CopyToDataTable

Xaml
UiPath.zip (1.5 KB)

Thanks,

5

@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

6

let me try and check

7

let me try and check

8

here i want to divide the sum by 100 as well

9

@T_Y_Raju

For your case you can use expression directly

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

image
image1330×532 31.8 KB

cheers

10

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

11

@T_Y_Raju

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

cheers

12

this expression will automatically write data to excel

13

@T_Y_Raju

This expression will give totals in datatable

You can use write range to write back

Cheers

14

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?

15

@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