Summing and Avg Excel Integer Values or Cell Values

Help Studio
, , ,
1

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?

pr
pr167×554 3.34 KB

Thanks.

2

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

3

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.

4

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

5

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

6

@ppr @Lak_Ui @jpbelchote

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)

rr
rr486×773 49.3 KB

and result
rr2

7

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

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
9

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
10

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

11

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.

12

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.

13

following implementation
grafik

Handling also blanks:
grafik

Has result:
grafik

which we can also format with dot seperator, by using a specific cultureinfo
grafik

As the default is using a comma as seperator
grafik

14

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