How to sum each data and get avg?

Help Studio
, , ,
1

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.

Datass
Datass695×519 217 KB

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.

2

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

3

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

SahibindenIstanbul2.xls (113 KB)

4

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
6

Hi @yigit.aybey

Try this approach

image
image699×609 22.6 KB 

(
	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
7

First of all thx for effort i appreciate that.

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

er
er1012×697 36.9 KB

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.

8

@yigit.aybey

Please import the following using the Import Panel

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

image

1 Like
9

It worked well bro. Thanks for help and effort.

1 Like
10

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