Can some help me on the Linq

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)

have a look on SUM and Average function from LINQ

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

Maybe we have to extra handle the number format

Hello @ppr

It showing an error

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

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)
1 Like

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

1 Like

@ppr

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

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

Perfect

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

1 Like

Thanks for the tutorial

How to AVG the sum value? @ppr

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)
2 Likes

Thanks @ppr it’s working

Follow the below link to find the tutorials about linq

1 Like

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