Calculate average of the column

Help
, , ,
1

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?
Thanks in advance.

column
column169Ă—700 3.07 KB

1 Like
2

simple use for each and then take the price value assign like this totalPrice = totalPrice+row(“Price”)

1 Like
3

@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
4

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
5

@bp777,

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
6

@sarathi125 @Vashisht
I get validation error with AsNumberable
asnum

7

Hi @bp777

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

Thanks
Ashwin S

1 Like
8

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

9

Hi @bp777

Have a try using string format activity and check it out

Thanks
Ashwin.S

1 Like
10

@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
11

@bp777,

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
12

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

13

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