 # 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.