none
how I can run excel funtions like max over datatable

    Question

  •   

     Dim dRange As New Data.DataTable()
            Dim connection6 As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("conn").ConnectionString)
            connection6.Open()
            Dim sqlCmd6 As New Data.SqlClient.SqlCommand("SELECT porosity_final  AS dX  FROM   DataInputSamples WHERE  PreKmax_Final > '0' AND FileID =  160035", connection6)
            Dim sqlDa6 As New Data.SqlClient.SqlDataAdapter(sqlCmd6)
            sqlDa6.Fill(dRange)
            connection6.Close()

            Dim ArrayPosority(TotSampl) As String


            Dim cont As Integer = 0

            For Each recod In dRange.Rows
                ArrayPosority(cont) = dRange.Rows(cont)("dx")
                Response.Write(ArrayPosority(cont))
                cont = cont + 1
            Next


            With excelApp.WorksheetFunction
                resultados = .StDev(ArrayPosority)  ---- in this sentence stdev not working over array, could you give me some ideas

            End With

    Wednesday, November 30, 2016 10:02 PM

Answers

  • Looks like you are trying to calculate the standard deviation of the elements in the ArrayPorosity array. Is that correct?

    If so, here is a way to do it, using lambda expressions:

    double media = resultados.Average();
    double somaDoQuadradoDasDiferencas = resultados.Select(val => (val - media) * (val - media)).Sum();
    double standardDeviation = Math.Sqrt(somaDoQuadradoDasDiferencas / resultados.Length); 
    



    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    • Marked as answer by Fredy68 Thursday, December 01, 2016 9:40 PM
    Wednesday, November 30, 2016 10:15 PM

All replies

  • Looks like you are trying to calculate the standard deviation of the elements in the ArrayPorosity array. Is that correct?

    If so, here is a way to do it, using lambda expressions:

    double media = resultados.Average();
    double somaDoQuadradoDasDiferencas = resultados.Select(val => (val - media) * (val - media)).Sum();
    double standardDeviation = Math.Sqrt(somaDoQuadradoDasDiferencas / resultados.Length); 
    



    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    • Marked as answer by Fredy68 Thursday, December 01, 2016 9:40 PM
    Wednesday, November 30, 2016 10:15 PM
  • Here is another way to do it, where you don't need to move the values to an array. You can do it directly using linq and canonical functions. Check it out on this MSDN article: 

    How to: Call Canonical Functions


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Wednesday, November 30, 2016 10:20 PM
  • Hello Mauricio, I am trying to make different statistical calculations, like intercept, slope, df, Devsq, average with excel funtions.

    Could you give me other idea.

    Thank you

    Oscar

     

    Wednesday, November 30, 2016 10:21 PM
  • I am going to use this option.

    Thank you

    Wednesday, November 30, 2016 10:28 PM
  • Hi Oscar,

    Welcome to the MSDN forum.

    It looks like your issue is solved, right?

    If you think Mauricio Feijo's solution is helpful, could you please mark it as answer and it will help other community members who have the same or similar issue to easier search this solution, thank you for your support.

    Best regards,

    Sara


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 01, 2016 8:25 AM
    Moderator
  • Hello Mauricio, could you give some idea where is my mistake. I want to use Statistical functions that excel I want calculate the slope and intercept, I want use the function linest and use Linq.

    this subroutine produce this  mistake. Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

    Code:

    Public Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
            Dim excelApp = New Excel.Application
            Dim db As New DBLRRPDataContext

            Dim varX = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.porosity_final > 0
                       Select datainputsamples.porosity_final

            Dim varY = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.PreKmax_Final > 0
                       Select datainputsamples.PreKmax_Final

            With excelApp.WorksheetFunction
                resultados = .LinEst(varY, varX)

            End With

            Response.Write(resultados)

        End Sub

    Thank you

     

           

     

       

    EndSub

    Thursday, December 01, 2016 7:06 PM
  • Hello Mauricio, could you give some idea where is my mistake. I want to use Statistical functions that excel I want calculate the slope and intercept, I want use the function linest and use Linq.

    this subroutine produce this  mistake. Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

    Code:

    Public Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
            Dim excelApp = New Excel.Application
            Dim db As New DBLRRPDataContext

            Dim varX = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.porosity_final > 0
                       Select datainputsamples.porosity_final

            Dim varY = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.PreKmax_Final > 0
                       Select datainputsamples.PreKmax_Final

            With excelApp.WorksheetFunction
                resultados = .LinEst(varY, varX)

            End With

            Response.Write(resultados)

        End Sub

    Thank you

     

           

     

       

    EndSub

    Hi Fredy,

    Sure . No problem.

    I have to ask you, however, to enter this question on another post. This Forum rule is just so when people search for questions it is easier for the search engine to classify it.

    So if you can please:

    1. Mark on this post the answer that helped you. ( Click on the Mark As Answer link. )
    2. Create another post with your new question. I know they are related, but its a different question, related to Linq and Advanced Functions. You can always paste a link to this post on the new post, if you wish.

    I will make sure to answer the new question there.

    Thank you.


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com



    Thursday, December 01, 2016 9:06 PM