visual basic in excel problem with average!newbie!:(

  • 13 years ago
    hi!my problem is like this:i have a total number of 15000 values in excel and i want with visual basic each time to take six of them and give out their average. e.x: the first six values:56,23,12,53,78,32 and their average is 42.33.so i want to do the same for the other 14994 values.but at once with a code.can you help me?iam a newbie  
  • 13 years ago

    Hi

    The way that I would approach this would be to first import the Excel data into a DataSet using OLEDB. There should be lots of code samples to do this

    Next, loop over the rows and calculate the averages.

    Suppose all the values are stored in column A, here's some very rough code:



            Dim dbl As Double

            Dim counter As Integer = 1

            For Each dr As DataRow In ds.Tables(0).Rows

                dbl += dr(0)

                If counter = 6 Then
                    'You can get the mean average here by dividing dbl/6
                    counter = 1
                Else
                    counter += 1
                End If




            Next

    Tim

  • 13 years ago

    hi tim thank you for your reply!can you explain me where i can find dataset?and what is oledb?and where it is?my data is on an excel sheet..where i will write the code you give me?i need to solve my problem for a degree-project i have in university..  

  • 13 years ago

    This is a quick and dirty approach to solve the problem :

     

    'Your project properties need a reference to "MS Excel 11.0 Object Library" !
    Option Strict Off
    Option Explicit On
    Imports Microsoft.Office
    
    Public Class Form1
        Dim Excel As Interop.Excel.ApplicationClass = New Microsoft.Office.Interop.Excel.Application
        Dim ws As New Interop.Excel.Worksheet
        Dim sum As Double
        Dim input_counter As Integer
        Dim output_counter As Integer
        Dim t As Integer
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Excel.Workbooks.Open("d:\entwicklung_vb8\temp\six.xls")
            Excel.Visible = True
            ws = CType(Excel.ActiveWorkbook.ActiveSheet, Interop.Excel.Worksheet)
            output_counter = 6
            For input_counter = 1 To 18 Step 6
                sum = 0
                For t = input_counter To input_counter + 5
                    sum = sum + CDbl(ws.Cells(t, 1).value)
                Next
                ws.Cells(output_counter, 2).value = sum / 6
                output_counter = output_counter + 6
            Next
            ws = Nothing
            Excel.SaveWorkspace("d:\entwicklung_vb8\temp\six_2.xls")
            Excel.Quit()
            Excel = Nothing
        End Sub
    
    End Class
  • 13 years ago

    Zorro suggests a good approach which would work very well for you. My only comment is that by using the Excel model object, you have to have Excel installed on the machine where you run the code (although this probably isn't an issue for you).

    DataSets and the System.Data.OleDb namespace are major components of ADO.Net, the mechanism used to access data from .Net. 

     

     

     

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Walking on water and developing software from a specification are easy if both are frozen.” - Edward V Berard