# How to sum each data and get avg?

Hell guys,

i need some help. I’m new at this platform. I do not know linq and V.B .NET(a little know).
This is my excel datas and it’s got more than 200 datas.

I need to get the AVG of each “City/Town” Price in different sheet. I cannot use data filter because of a lot datas.
Notes : Some datas has got space. I mean for example " Bulgurlu " like that.
in Price, There is a dots between numbers and end of the TL(String)

So can u help me?? If you need more info i can explain.

Thanks.

Hello @yigit.aybey Can you please share the sample input excel

This is my Excel File.
NOT : There are lots of sheet name, you can ignore them or delete.

SahibindenIstanbul2.xls (113 KB)

Hey!

Try this…

Max:

``````DT.AsEnumerable().Max(Function(row) cint(row(“ColumnName”)))
``````

Min:

``````DT.AsEnumerable().Min(Function(row) cint(row(“ColumnName”)))
``````

AVG:

``````DT.AsEnumerable().Average(Function(row) cint(row(“ColumnName”)))
``````

Reference:

Regards,
NaNi

1 Like

Try this approach

``````(
From row In dt_Data
Group row By
k= Strings.StrConv(Regex.Replace(row("City/Town").ToString, "\s+", " ").Trim, VbStrConv.ProperCase)
Into grp=Group
Let avg = grp.Average(Function(gr) Double.Parse(gr("Price").ToString.Replace("TL", "").Trim(), CultureInfo.GetCultureInfo("tr-TR")))
Let sum =  grp.Sum(Function(gr) Double.Parse(gr("Price").ToString.Replace("TL", "").Trim(), CultureInfo.GetCultureInfo("tr-TR")))
Select dt_Result.Rows.Add({k, grp.Count, sum, avg})
).CopyToDataTable
``````

xaml for reference

CityWiseAvg.xaml (8.0 KB)

1 Like

First of all thx for effort i appreciate that.

Secondly, i did your way but i got this error message

My variables :

My_Datas => Read Range Output(DataTable)
New_Dt => Build Data Table Output(DataTable)

My_Code :

``````(
From row In My_Datas
Group row By k = Strings.StrConv(Regex.Replace(row("City/Town").ToString, "\s+", " ").Trim, VbStrConv.ProperCase)
Into grp = Group
Let avg = grp.Average(Function(gr) Double.Parse(gr("Price").ToString.Replace("TL","").Trim(), CultureInfo.GetCultureInfo("tr-TR")))
Let sum = grp.Sum(Function(gr) Double.Parse(gr("Price").ToString.Replace("TL","").Trim(), CultureInfo.GetCultureInfo("tr-TR")))
Select New_Dt.Rows.Add({k, grp.Count, sum, avg})
).CopyToDataTable
``````

Thanks.

@yigit.aybey

Please import the following using the Import Panel

1. System.Text.RegularExpressions
2. System.Gloabalization

1 Like

It worked well bro. Thanks for help and effort.

1 Like

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