Summing and Avg Excel Integer Values or Cell Values

Help Studio
, , ,
8

give a try at following and also have a check on the below comment:

Assign activity
LHS: PriceSUM | Double
RHS:

(From d In YourDataTableVar.AsEnumerable
Let chk = Not isNothing(d("Price")) OrElse String.isNullOrEmpty(d("Price").toString.Trim)
Where chk
Let m = System.Text.RegularExpressions.Regex.Match(d("Price").toString, "[\d,.]+")
Where m.Success AndAlso Double.TryParse(m.Value, Nothing)
Select x=CDbl(m.Value)).Sum(Function (x) x)

We do defensively

  • looping over all rows
  • filter out empty values
  • extracting the number with regex
  • check if we can parse
  • parse the value to Double
  • sum up all extracted values

Just take it as a modelling and feel free to decompose the LINQ to essential activities
Keep in mind we sorted out the unparseable values

Similar we can do for the average:

(From d In YourDataTableVar.AsEnumerable
Let chk = Not isNothing(d("Price")) OrElse String.isNullOrEmpty(d("Price").toString.Trim)
Where chk
Let m = System.Text.RegularExpressions.Regex.Match(d("Price").toString, "[\d,.]+")
Where m.Success AndAlso Double.TryParse(m.Value, Nothing)
Select x=CDbl(m.Value)).Average(Function (x) x)

Number Parsing:
Values in Excel Worksheet visually presented to us are not mandatory the same when we do the read range. In the sample we do see the thousand seperator as dot. Does mean:
grafik

Also with the risk:
grafik
Wrong parsing or failings

we can handle this with the cultureinfo as shown above. Also we would recommend to do include an additional checking on the format and which culture info is needed as also replacements on string base can have side effects:
grafik

Similar what was done for the date the same anylsis techniques you can apply for the numbers
:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

3 Likes