Can some help me on the Linq

[Marian_B (Marian)]

Hello

I need to sum the value and get the Average of all the summed value
@Yoichi @ppr @THIRU_NANI @pravin_calvin @Sudharsan_Ka @Gokul001

Here is sample excel file

Sum.xlsx (8.0 KB)

[ppr (Peter Preuss)]

have a look on SUM and Average function from LINQ

Average Lambda Expression Sample in C#

Sum Lambda Expression Sample in C#

give a try on dtData.AsEnumerable.Sum(Function (x) CDbl(x(0).toString.Trim))

Maybe we have to extra handle the number format

[Marian_B (Marian)]

Hello @ppr

@ppr

dtData.AsEnumerable.Sum(Function (x) CDbl(x(0).toString.Trim))

It showing an error

Message Box: Conversion from string “” to type ‘Double’ is not valid.

[ppr (Peter Preuss)]

@ppr

Maybe we have to extra handle the number format

as mentioned. In your case you should filter out the rows with empty values as the message is indicating that an empty string cannot be converted into a Double

As an alternate we can do:

(From d in dtData.AsEnumerable
Where Not (isNothing(d(0)) OrElse String.IsNullorEmpty(d(0).toString.Trim))
Select v = CDbl(d(0).toString.Trim)).Sum(Function (x) x)

[Sudharsan_Ka]

HI @Marian_B

You can also try with excel formula

Steps are
- Read Range store in DT1
- Write cell in the cell you want
- txt will be “=SUM(A2:A”+(DT1.rows.count-2).ToString+“)”
- Write cell in the cell you want
- txt will be “=AVERAGE(A2:A”+(DT1.rows.count-2).ToString+“)”

Hope you get one alternate also

Regards
Sudharsan

[Marian_B (Marian)]

@ppr

(From d in dtData.AsEnumerable
Where Not (isNothing(d(0)) OrElse String.IsNullorEmpty(d(0).toString.Trim))
Select v = CDbl(d(0).toString.Trim)).Sum(Function (x) x)

@ppr

[ppr (Peter Preuss)]

please run the LINQ only over valid data. It looks that you are running it over the shared Excel where you marked SUM, AVG in some Cols. This is reported by the exception - a string value “SUM” cannot be converted into Double.

When starting with LINQ we would recommend to do the self training / exploration over sample values in a datatable (done with Build datatable). Once it is running then it can be developed ahead more close to the targeted scenario

[Marian_B (Marian)]

Ok @ppr , I have got the Sum value from the above Linq.

I need to Average that value. Can you share any learning tutorial for linq

[ppr (Peter Preuss)]

@Marian_B

I have got the Sum value from the above Linq.

Perfect

see the links from above and also refer to the learning catalogue

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operator [HowTo] Overview Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start…

[Marian_B (Marian)]

Thanks for the tutorial

How to AVG the sum value? @ppr

[ppr (Peter Preuss)]

@ppr

(From d in dtData.AsEnumerable
Where Not (isNothing(d(0)) OrElse String.IsNullorEmpty(d(0).toString.Trim))
Select v = CDbl(d(0).toString.Trim)).Sum(Function (x) x)

similar, just using the average method:

(From d in dtData.AsEnumerable
Where Not (isNothing(d(0)) OrElse String.IsNullorEmpty(d(0).toString.Trim))
Select v = CDbl(d(0).toString.Trim)).Average(Function (x) x)

[Marian_B (Marian)]

Thanks @ppr it’s working

[Sanjit_Pal]

Follow the below link to find the tutorials about linq