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

### 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.”