# Summing and Avg Excel Integer Values or Cell Values

Hi all,

i have a price of excel file and i need to sum all of them. How can i do that?
Also, i need the get avg of this total price?

Thanks.

You can only sum and get the average from numbers. The “TL” makes the cell be readed as text.

You can store these data to a datatable and remove the " TL" and then sum and get the average

Hımm. Is it possible to without removing the “TL” because it should be there or seeing by users. Or i can delete tl and summing the values after i can add the “TL” again. But i do not know how to write this code. Deletion tl and summing the datas.

Thanks.

in the excel file you can keep it as such and while reading it as datatable inside the code remove TL and perform the summing

we can let the TL in the excel and can do the sum / avg on datatable base. Here we can implement quickly a logic which is using only the digits and will ignore the TL

Have a look on regex as well

ok. i deleted TL and here is my form like that.
in 1st assign activity is => EraseTL = CurrentRow.Item(0).ToString
2nd is => EraseTL = EraseTL.Substring(0,EraseTL.Length-2)

and result assign erasetl=0 outside loop and inside loop assign EraseTL = EraseTL +Cdbl(CurrentRow.Item(0).ToString)-> to get the sum

and outside the loop EraseTL /Price_Dt.count will give you the average

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: Also with the risk: 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: Similar what was done for the date the same anylsis techniques you can apply for the numbers  [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

3 Likes

Hi @yigit.aybey,

I prepared a little simulation for you. You can adapt this to your own project. The suggestions shared by my other friends are also very valuable. I suggest you take a look.

sumPrices.xaml (8.2 KB)

Regards,
MY

1 Like

My EraseTL variable is ‘string’ i need to convert double or should i define a new variable which can be double or smt else?

I did your 1st method but the result always same or i did smt wrongly. What i’ve done is i =>
Assign- PriceSUM(Double)

``````(From d In Price_Dt.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)
``````

For the seeing the result. i put the log msg and i wrote PriceSUM.ToString
I’m getting the same result but not true.
I changed the place of PriceSUM assign i mean i put out of the for each but still i’m getting same result.

I tried your code and worked.
Thanks for that.

And
result => 499351
I want to do like that => 499.351
How can i do that

Thanks.

following implementation Handling also blanks: Has result: which we can also format with dot seperator, by using a specific cultureinfo As the default is using a comma as seperator This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.