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

• • Reply

  13 years ago

  by giaroumpis

  kastro

  , Greece

  Joined 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  
  Report abuse
• • Reply

  13 years ago

  by TimL

  Tim Leung

  Berkshire, United Kingdom

  Joined 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
  
  

  Report abuse
• • Reply

  13 years ago

  by giaroumpis

  kastro

  , Greece

  Joined 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..  

  Report abuse
• • Reply

  13 years ago

  by Zorro_ot

  Oliver Tell

  Hamburg, Germany

  Joined 15 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

  Report abuse
• • Reply

  13 years ago

  by TimL

  Tim Leung

  Berkshire, United Kingdom

  Joined 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. 

   

   

   

  Report abuse