# Calculate average of the column

Hello all,
I need to calculate average of the Price Column and show calculated price in Message Box. I found some possible solutions, but they seem too complicated. Is there a neat way of doing this?

1 Like

simple use for each and then take the price value assign like this totalPrice = totalPrice+row(â€śPriceâ€ť)

1 Like

@bp777

loop through and get the data from each row and add the data. finally divide the sum by number of data in the table.

try the below solution may works for you

sum = 0
count = dt.rows.count
for each  row in Dt:
{
sum = sum+ cint(row("Price").replace(".",",").replace(",",".").substring(0,row("Price").length-2).trim))
}

Average = sum/count

Ajay

1 Like

Hi @bp777,

1.Calculate the sum of the values in the column first using this in assign activityâ€¦

sum= dt.AsEnumerable.Sum(Function(x) If(IsNumeric(x(â€śColumn2â€ť).ToString.Trim),CDbl(x(â€śColumn2â€ť).ToString.Trim),0))

sum is of type System.Double.

2.Then divide that sum with no of rows present you can get that fromâ€¦

num=dt.rows.count

Average = sum/num
3. You can keep this in the message box as Average.tostring.

If you find it useful let me knowâ€¦

Any doubts let me knowâ€¦

Cheers.
Vashisht.

1 Like

Use this linq query by reading the excel into a datatable named as dtExcel

dtExcel.AsEnumerable().Select(Function(r) CInt(r.Field(Of String)("Price").Replace(".",",").replace(",",".").Substring(0, r.Field(Of String)("Price").Length-2).Trim)).ToList().Average
1 Like

@sarathi125 @Vashisht
I get validation error with AsNumberable

Hi @bp777

Use Dt.AsEnumerable try to import namespace as System.enum

Thanks
Ashwin S

1 Like

@AshwinS2 , @sarathi125 , @Vashisht
Nice, seems like itâ€™s going well, but now I need to remove dots from Price(since itâ€™s double).

Hi @bp777

Have a try using string format activity and check it out

Thanks
Ashwin.S

1 Like

@bp777
Good to hearâ€¦ If you problem is resolved mark as solution and close the threadâ€¦

Any doubts let me knowâ€¦

Cheers.
Vashisht.

1 Like

Check with this one,

dtbExcel.AsEnumerable().Select(Function(r) Decimal.Parse(r.Field(Of String)("Price").Replace(".", string.Empty).Replace(",", ".").Replace("â‚¬", string.Empty))).ToList().Average
1 Like

Wow @sarathi125.
Amazing solution.
Thank you very much!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.