Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Function Average Using VBA


I would like to calculate automatically and using VBA the average
of my results. Please have a look at my file to see what I really
would like to create.

Thanks a lot!


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Average Function Using Forms
I have made a user form that requires the user to input 9 values in nine text boxes. I have named the text boxes txtcp1, txtcp2, txtcp3 .... txtcp9, I then want to get the average of the values that were inputted into the text boxes and call that value "txtcave"

I have tried different versions of the average formula but still cannot get it to work, I always get a "runtime error 13" or the value does not get inputted into the text box as a number.

cave = "=average((txtcp1.value) , (txtcp2.value) , (txtcp3.value) , (txtcp4.value) , (txtcp5.value) , (txtcp6.value) , (txtcp7.value) , (txtcp8.value) , (txtcp9.value))"
txtcave.value = cave

Please help, I have run out of ideas how to do this.

Max Number Of Arguments In Average() Function

Please don't laugh with me


Originally Posted by MSDN

Number1, number2, ... are 1 to 30 numeric arguments for which you want the average.

How come (see the attachment) that it doesn't seem to be the case?


VBA Excel Function To Average Count
Good afternoon people.

I am trying to create a function which selects a range (ie. a1:a10) which has integers 1-100. Some of the cells contain 0 and I want to total the cell amounts that are > 0 and count how many cells do not contain 0. I am trying to create an average for example 10 cells 5 have 0s and 5 do not. The values in the 5 cells are 10, 20, 30, 40, and 50. The number of cells is 5 and the average of the 5 cells is: 30. Any suggestions? I need this to work for multiple columns A,B,C,D, etc. this is where I am getting hung up. Thank you, DAVE

Average If?
Hello all,

I'm in a bit of a quandary here. I posted before a while back wondering how to sum numbers that were within a certain range (I found that I should use the SUMIF function). But is there a way that I can average a group of numbers that meet a certain criteria? Specifically, I have a list of positive and negative numbers all together, is there a way to average only the negative numbers that are in the list and just the positive numbers that are there?

Jason O

i wanna create a program that reads in up to 100 marks from using inputBoxes the program has to continue prompting until the user types an X as input. but i donno how to do it but this is what ive done so far.

Private Sub cmdGetMark_Click()
Dim intMark(9) As Integer
Dim StrMark As Integer
Dim IntCount As Integer

StrMark = InputBox("Enter Mark")

While Not StrMark = "X"
intMarks(IntCount) = Val(StrMark)
Next X

StrMark = InputBox("Enter Mark")
IntCount = IntCount + 1

End Sub

Private Sub Form_Load()

End Sub

> Average <
Hi, I have made a program. I want to take the average of numbers. I know how to average numbers, of course . But if I get too much numbers to average, I will get an overflow. How can I prevent this?

Grtz, Spyru

SQL Average
I'm trying to find the average for the field Score 1 in my Access DB to be displayed in a text box when I click a cmd button. I've set up the below SQL query without any luck. Can someone help me out here.

Private Sub Command2_Click()
Dim ScoreQ As String
Dim strCn As String
Dim rsInformation As ADODB.Recordset

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;" & "Data Source=C:Golf Statsstats.mdb"

ScoreQ = "Select Avg[Score 1] FROM stats"

Set rsInformation = New ADODB.Recordset

Text2(23).Text = rsInformation![Average]
End Sub


Get Average Using SQl
im using Access Db,

how to get the average of the "Amount" (Currency type)?

SQL Average
I'm new to SQL so please be patient with me
I'm trying to get the average for a field and display the result in a text box. Below is the code i'm using but with no luck. Can anyone help me out please????

Private Sub Command2_Click()

Dim ScoreQ As String
ScoreQ = "Select Avg[Score 1] as Average FROM Stats"
Text2(23).Text = ScoreQ

End Sub


How can i find the average of a group of numbers.
The group of numbers can be different lengths so i cant just do total/6


Average RGB Values??
Hi this is my first post, be gentle

Ok i am trying to detect the silhouette of an object in a image. At the moment i'm using getpixel to extract the RGB values of the object the user want to extract from the background (assuming it is a different colour to the background).
This works fine if the object happens to be 0,0,255 all over, because i can then ignore everything that is not 100% blue and i should have the object. The problem is that an object in a photo etc will never have all pixels the same colour.
So i tried averaging the rgb values around the point the user clicked, but there may never be a pixel that is this average colour.

Is there a way to say : If selectedpixelcolour between colour1 and colour2 then ......

(if you use Photoshop i want it to work like the magic wand select tool )

sorry if that made no sense

Creating An Average
Hi all, I´m brand new here, and a complete novice!
I am building an excel doc to accumulate daily results - these results are linked to another page were they display up to date totals of each month
I need to display an AVERAGE TOTAL included with each month
But i need to have it so that it divides by the amount of days in each month depending on which day its up to, to give the correct average
Also, I need to display an up to date yearly average. The figures start on the 28th of March so it is not a complete year.
Can anyone help me with this?
Thanks a lot

Average Value Of Range?
Hi guys. I'm trying to get the average of a range of cells. I want blanks to be zero values of the specified range. The code below works, there must be a way to do this without using a loop.

Function blankavg(range1 As Range)

For Each cell In range1
num_pts = num_pts + 1
blankavg = Application.WorksheetFunction.Sum(range1) / num_pts

End Function

Average Over Several Sheets
Visual Basic 6.0 w/MS Excel

I have a workbook with three sheets. The names of the sheets are Test1, Test2 and Test3.
At the same cell address on each sheet there are values that I wish to find the average and standard deviation.
For example, at cell address B4 on each sheet are the following values:

Test1 – 2
Test2 – 9
Test3 – 4

I know that if I use the following formula in a macro I can get the average value of 5:

Activecell.FormulaR1C1 = "=Average(‘Test1’!B4,’Test2’!B4,’Test3’!B4)"
Here’s my question. The above example uses three sheets. Is there a general statement that I can include in a macro that covers the case for finding the average having N sheets in a workbook?

Help Calc. Average

I have 10 text boxes, each containing a number.

I want to calculate the Average, BUT... If any textbox contains a "zero" or "blank", I DON'T want that textbox included in the Average Calculation.


1 + 2 + 3 + 0 + 4 + 5 + 0 + 6 + 0 + 7

= 28 divided by 7 textboxes (not 10, because 3 textboxes contain a 0)

= Average of 4

I'm trying to code with if/when statements... but I am having no luck.

Any suggestions?

Calculate Average

I am writing a little macro to calculate some data in Excel.

The first column contans date's end the second colomn cotains data.

If the date's are the same in column 1, I would like to add up the values in column 2 and average them and display that value in column 3 on the first date.

Column 1

column 2

This would be the result:

Column 1 Column 2 Column 3
1-1-2007 10,00 11,67
1-1-2007 15,00
1-1-2007 10,00
2-2-2007 12,00 19,60
2-2-2007 20,00
2-2-2007 21,00
2-2-2007 22,00
2-2-2007 23,00

I've started with some code but o luck....I think it's a though one. Ay advice how to start?

Thanks in advace,


Average Problem
I have this problem and would like to learn how I can handle it. There is a column of cells with values running like (123,00000,324,00000,43,00,312,000,23443). In short i have numbers in a clumn interrupted by varying-length zero colums. More clear example:
I tried to make an average of the whole range(including zero subcolumns) and got very little numbers because i think Excel divides the total sum by a number of ALL cells in a column. I want to ask you to help me make a formula which can make an average of only non-zero cells in this column.
Many thanks in advance

Average Program
I'm trying to write a program that displays the average of three test marks in a label, pretty basic but there is a bug in it some where and I can't seem to put my finger on it, could I get a little help please? thanks in advance.

Private Sub cmdCalculate_Click()

'assing marks to veriables
MarkA = vsbA.Value
Markb = vsbB.Value
MarkC = vsbC.Value

'calculate average
Average = (MarkA + Markb + MarkC) / 3

'display average mark
lblAverage.Caption = "your average is" = Str(Average)
End Sub

for some reason when I put the three marks in the text boxes and click calculate it puts false in the label.

How Would I Figure Out An Average?
Lets say we have these stats:

min 1) 15 dots
min 2) 43 dots
min 3) 5 dots

How would I find out the average of dots per minute?

Average (mean) Score
edit: sorry let me be more clear
so far below, purpose of it is to determine's a person (freegumpher's) handicap, they must play atleast 3 games to determine that

adding to it is the loops below, the part i'm lost is that if their is more than one player, i need to determine the average (mean) of all the freegumpher's that inputted their scores here
thanks for the help

this is the code of what i have so far,

//Question 1: did you play at least 3 games?
//Question 2: Is there another player?
//ErrorMessage = “Answer must be Y or N, Please try again”

1If Question = "Y" or Question = "y" Then
2Do While Question <> “Y” or “y” or “N” or “n”
3Question = InputBox(ErrorMessage)
5Input Question2
6While (Question = "Y" or Question ="y")
7INPUT Score1
8INPUT Score2
9INPUT Score3
10Total_Score = Score1 + Score2 + Score3
11Average = Total_Score / Games
12Display Score1
13Display Score2
14Display Score3
15Display Total_Score Display Average
//Handicap Calculation
16If Average >= Par Then
17Display "You have no handicap"
18 Else
19Display "Your Handicap is "; Par - Average
20End If
21Counter = Counter + 1
22 End While
24Display "You have not played enough Freegump to determine a handicap"

Calculating Average
I have seven text boxesand after a certain function is performed, these text boxes will each contain a value. However, not every box is going to be filled with a value but I need to calculate the average of the values in the text box, but cant just use a straightforward equation of sum = (text1+text2...text7)/7 as i dont know how many of the text boxes will contain a value each time.
Any help with this is much appreciated!!!

Need Help In Finding The Average
Hello everyone. I'm a newbie to VB. However I have exerience with RapidQBAsic before.

I wanted to calculate an average but the program will give false answer if I don't enter a value at the third box. This is because I divide the values of the first three boxes by 3.

How can I correct the code so that the program will divide by 2 instead of three if I leave the third box empty?


Private Sub Command1_Click()
one = Val(Text1.Text)
two = Val(Text2.Text)
three = Val(Text3.Text)
average = (one + two + three) / 3
Text4.Text = "Average is" + Str$(average)
End Sub

Average Of Group By
Having trouble figuring out how to do this:

I have a table called 'tblTransactions'. In that table are two fields (among others) 'Account' and 'CashTrans'. There are many entries of the same account.

What I would like to do is Group all of the same account numbers together where the accounts appear at least 5 times, then get the average of their sales.

I tried many different ways and i just cant get it to work. I thought I could:

Group By Count(Account) > 5
and many other variations of that and no luck. Any suggestions?

Finding Average Max Min ????????
hi all

im a new member here hope that i would learn from here all what i need in vb but i have a little assignment i wish i could get some help in writing the code the question is

write a simple program that allows the user to input 5 numbers and find the maximum minimum sum and average of these number

the version of my program is vb 6.0

Weighted Average
How do I calculate a weighted average ? Lets say I have an array of many amounts. How can I find out the weighted average of this list. There is no selection criterion - like those in a certain range, although that requirement might be next.

Average App. Im Lost Help Me Plz
ok this is my last program of the day and its really ticking me off. I have like 15mins to finish it before i goto work. Im confused.
i made the form here it ishere
what i need to do is type a number in the text bow then when i click start a msgbox needs to appearand i need to enter a number then click next in the msg box and another msgbox saying the same thing needs to come up na dso on. then the average is suppose to display in the label.
for example if i type 5 in the textbox and click start then a msgbox needs to come up asking me to type in a number then i enter one and click next on the msg box and then 4 more boxes come up and then on the last box when i click next i need to see the origional screen w/ the average in the blank label.

Easy Average
Why can't I do like this? Do I need to write an own program to get the average between to random in parameters?

Sub returnMean(firstRow, lastRow)
' in this case the in parameters is
' firstRow = 1300
' lastRow = 3493
ActiveCell.FormulaR1C1 = "=AVERAGE(R[firstRow]C[-2]:R[lastRow]C[-2])"

End Sub

Thanks for answering to me - "the hopeless coder"

Average Cells

I would like to be able to access a txt file that contains a list of values that are comma delimted ie

etc etc

I want to get to the bottom and average the three columns. Then dump these three new numbers to another txt file.

Any ideas


SQL Round And Average
I've got an SQL query that gets and average of several numbers and displays them in a listview. No probs there.

Thing is, don't like the messy look of 4.3333333333333333 in the listview, and only need 2-3 dec places in the numbers. I've tried throwing ROUND around the avg string like

ROUND(Avg(scores), 2) with the 2 specifying the number of dec places, but I get an invalid function error when trying that...

Any ideas?



Calculating An Average
I'd love some help if anyone recognizes a possible error in my VB code...
I'm trying to write a program which calculates an average grade. I have two projects worth 50 points and two finals worth 100 points.
I've written the code but when I click the button the letter grade does not display as intended.
Anyone know why?

Private Sub cmdDisplay_Click()
Dim intEarned As Integer, intPossible As Integer
Dim sngRatio As Single, intCount As Integer
For intCount = 0 To 3
If chkDone(intCount).Value = vbChecked Then
intEarned = intEarned + Val(txtScore(intCount).Text)
Select Case intCount
Case 0 To 1
intPossible = intPossible + 50
Case Else
intPossible = intPossible + 100
End Select
End If
Next intCount
sngRatio = intEarned / intPossible
Select Case sngRatio
Case Is >= 0.9
lblGrade.Caption = "A"
Case Is >= 0.8
lblGrade.Caption = "B"
Case Is >= 0.7
lblGrade.Caption = "C"
Case Is >= 0.6
lblGrade.Caption = "D"
Case Else
lblGrade.Caption = "F"
End Select

Any and all help would be greatly appreciated.

Average Of 3 Numbers?
I have 3 txtboxes that i enter 3 different test grades in and i want to get the average of the any snippets that will help me out.........thanks in advance

Finding Average
OK, Monday morning brain fog has got me.
I have the following text in a file. My task is to get the average throughput of each file type per hour. eg: - tiny-file occurs 3 times in the 10:00 hour, with three different throughput values - so the average throughput would be - 87.26

tiny-file.bin220.098/13/02 10:14
big-file.bin23.538/13/02 10:24
med-file.bin13.618/13/02 10:25
small-file.bin10.378/13/02 10:26
tiny-file.bin4.248/13/02 10:36
big-file.bin29.58/13/02 10:44
med-file.bin29.638/13/02 10:45
small-file.bin28.678/13/02 10:45
tiny-file.bin37.448/13/02 10:55
big-file.bin26.728/13/02 11:04
med-file.bin15.128/13/02 11:05
small-file.bin13.478/13/02 11:06
tiny-file.bin30.648/13/02 11:16
big-file.bin25.998/13/02 11:24
med-file.bin20.148/13/02 11:25
small-file.bin32.378/13/02 11:26
tiny-file.bin42.838/13/02 11:36
big-file.bin21.98/13/02 11:46
med-file.bin35.178/13/02 11:47
small-file.bin37.228/13/02 11:47
tiny-file.bin44.728/13/02 11:57
So far, I'm reading the file in line by line, finding the first time value that the file occurs, and then I'm stuck. I know what I want to do mathematically, I'm just not sure how to find how many times the file occured in the hour, and work with the numbers from there. Hopefully, this made sense. This project just found it's way to my desk with a big red flag on it. I hate red flags, they're even worse on Monday mornings.

Thanks in advance.

Average Without Variables
I have to come up with a way to input, store, and average MPG (Miles Per Gallon) in VB without using variables. I had planned on using a ListBox. Basically what I wanted to do was txtMiles / txtGallons=txtMPG, lstAverage.AddItem txtMPG. Is there a way to add the total lines in a ListBox? If you could then it would simply be txtAverage=(total of all lines in ListBox)/lstAverage.ListCount ... Is there a way to do this? And if not, what would be a good way to average without using variables?

Average Pixels?
Here is a quick run down of what I am trying to do:

I am trying to write a program that eliminates between a fifth to a third of the pixels in an image inside a picture box and display the smaller image as another file. Everything works fine except sometimes there are patches of color that stand out due to only taking every fifth pixel. Is there a way to a) average pixel colors so that (maybe taking the eight surrounding pixels around the main pixel) or b) shrink the image to where there are only a fifth of the pixels to begin with? I would prefer the earlier of the two, but anything would be wonderful. Thanks for any help!

Average Calculator
I need to use the following attached form, the buttons are control arrays and I need to average the numbers. I have to create a variable that will hold the values that I enter and then give me the total when I push enter....Could someone start me off. Thanks

Average Calculator
Could some do the following:

Make a program that will allow the user to enter up to ten digits and then average them. The numbers entered can have more than one digit and can have only one decimal point.


1.Use a procedure in a standard module to display the digit on the screen.

2. Use a constant for the upper limit of the array.

3.The user should not be allowed to store more than 10 numbers.

4. The users should only be allowed to press cmdAverage if there are numbers in the array.

5. the user should only be allowed to enter one decimal point per array.

6.If the display area is blank the user should not be able to store a value. (I think you should use the Len() function)

Thats it, Anyone want to try?

Average Question
How do I find the average of a set of values in an array?

Or is there even an easier way of doing what I want?? I want to have a series of values and as each new value is added, to have the average value updated.

Any help guys?? Thanks ...

Also ... what happened to the search function on this site ?? I miss it.

Average Algorithm
I have a sample with 1500 numbers with an average of 20. I want to divide this sample in 5 groups, 1st with 346 numbers, 2nd with 584, 3rd with 37, 4th with 97 and 5th with 533 and I want every single group with the same average of the sample (20).

Calculation Of Average
Hi veena,

Can u tell me how we can calculate the correct average in report for

But in that case average was wrong actual average is 13.33, I have to ignore
the 0 while calculating the average.
Average = Total / no. of month i.e. 120/10
But in the month of Aug value is 0 then the avg should be 120/9 = 13.33
Can u help me how I use the formula to remove the 0 while calculating the
average in crystal report.


Average Of Two Numbers
I have a column in my access database table and I want to add  another column with average of every two numbers, like that:
Field1        field2
A1           â€śâ€ť
A2        (a2+a1)/2
A3        (a3+a2)/2
A4        (a4+a3)/2
An        (an+a(n-1))/2     
Can everybody help me, please?

Average Calculation
I have 2 tables A and B
A is master with field1 as primary
In B this is the foreign key

Table A content
Fld1        fld2
1    10000
2    15000
3    12000
Table B
    1        5000        true
    1        5000        false
    2        2000        true
    2        3000        false

what I want is the average?
Table A is contract details with contract no and contract amount
Table B is contract payment details, values which are True are realized amount.
I need to find the average
Avg=(total of realized amount / count of contracts)

Can any one please help me
John philip


***  Even the Best, did the Bad and Made the Best ***

John Philip

Calc Average MPH
Hello o'wise ones
I'm trying to write a program to emulate my van's computer that displays average MPH, gas mileage, miles left in tank and so on. I'm having difficulty with the formula for the ave MPH. I have a button that controls the speed, it increments a progress bar in 10 MPH increments. Another to decrement. Each MPH block 10, 20, weighted. In other words I give each a value to multiply by the time spent at each block. 10 is given 1, 20 is given 2, 30 a 3...

So, when accelerating to 30 MPH it could go like 2 seconds at 10 MPH, 3 seconds at 20 MPH, 5 seconds at 30 MPH. I hit the calc button and for 10 MPH a 1 is multiplied by 2 seconds + (20 MPH) a 2 is multiplied by 3 seconds + (30 MPH) 3 is multplied by 5 seconds. It is put into a total then divided but how should it be divided. I know my method isn't right.

thanks for any help, suggestions, criticism, laughs.....

Private Sub cmdCalcAveSpeed_Click()
' stop all timers
Timer0.Enabled = False
Timer10.Enabled = False
Timer20.Enabled = False
Timer30.Enabled = False
Timer40.Enabled = False
Timer50.Enabled = False
Timer60.Enabled = False
Timer70.Enabled = False
Timer80.Enabled = False
Timer90.Enabled = False

' time as a number to calc ave speed
   Const VTime0 As Single = 0.5
   Const VTime10 As Integer = 1
   Const VTime20 As Integer = 2
   Const VTime30 As Integer = 3
   Const VTime40 As Integer = 4
   Const VTime50 As Integer = 5
   Const VTime60 As Integer = 6
   Const VTime70 As Integer = 7
   Const VTime80 As Integer = 8
   Const VTime90 As Integer = 9
' calculations
' total multiplier time at each speed
   Total0 = VTime0 * Time0
   Total10 = VTime10 * Time10
   Total20 = VTime20 * Time20
   Total30 = VTime30 * Time30
   Total40 = VTime40 * Time40
   Total50 = VTime50 * Time50
   Total60 = VTime60 * Time60
   Total70 = VTime70 * Time70
   Total80 = VTime80 * Time80
   Total90 = VTime90 * Time90
   TotalTime = Total0 + Total10 + Total20 + Total30 + Total40 + Total50 + Total60 + Total70 + Total80 + Total90
   DivV = TotalTime / 10 ' this division approach can't be right
   lblAveS.Caption = DivV
End Sub

Getting Average , Do I Need Access Or Only VB
Maybe not a question specific for this forum but I don't know where to start.
Because of this forum I was able to make a program to write data to an access tabel.
But my next problem is how to get it back in my VB program.
I've created a program that writes every 5 minutes a temperature to accessDB with in the columns year, week, day, temp1, temp2, temp3, temp4 so I have 7 columns and now I want to calculate the average of one day and show it in my VB program. Also I want to be able to select witch day of week and year I want to see and if I want to see temp1 ,2, 3, 4.
Now my problem (I know nothing about access, and a little bit more about VB) do I need to create something(query, form) in access or can it be done in VB or make a form in access and load it in VB. I really don't know how and where to start.
Hope someone can help.

Erik Koole

Edited by - yertle on 3/12/2004 4:45:49 AM

Getting An Average Color Of A Whole Picture
Hi, i need to sort regions of pictures (tiles) by color. The problem is that i don't know how to calculate the average color of a picture.

This is the code i have, but it doesn't seem right at all (pictures with even no explicit green in it have higher values than those who are almost fully green)

Function GetAverageGreenOfObject(reg As Borders, pic As PictureBox) As Long
Dim i As Integer
Dim j As Integer
Dim green As Long
Dim ave As Long
Dim count As Long
green = 0
count = 0
For j = reg.Top To reg.Bottom
For i = reg.Left To reg.Right
'if transparent color, skip it
If GetPixel(pic.hDC, i, j) <> RGB(255, 0, 255) Then
green = green + GetGREEN(GetPixel(pic.hDC, i, j))
count = count + 1
End If

green = green / count

GetAverageGreenOfObject = green
End Function
where reg is a region (left, right, top, bottom) of the given picturebox pic

Using An Array To Find An Average
I'm needing help with the following...Here is the setup...
A user will enter two zip codes via TextBox's into cells A1 & B1...In cell C1 I need a formula to calculate the average time for JUST those zip codes. These zip codes will be found in two columns that are continually being added to...(a sample is shown below). I have thus far tried the following array formula's...the "IF/OR" gets me close...But as any formula GURU will quickly figure out... once the "IF" test comes back true it will give the average for the whole list of times and not just those attributed to the zip codes entered.
Cell A1 = 94876
Cell B1 = 96577
The average should come back as: 7:24:16



Zip Code 1Zip Code 2Time
94876 96577 8:00:00
94876 95862 0:32:20
95648 95862 6:16:24
95648 95862 7:16:24
94876 96577 6:48:32
Thanks for the guidance

Calculate Ongoing Average
Hi guys!

Can anyone tell me how to calculate an ongoing average?

I have a game that supports multiple user profiles. For each session a user plays, they get a session score from zero to 500. I want to have a top-ten list that keeps the best ongoing average score.

I thought of saving an averageOverallScore variable. But how do I update it efficiently? I know I could save totalPointsEarned and totalSessionsPlayed and then do:

averageOverallScore = totalPointsEarned / totalSessionsPlayed

...But this seems bad because wouldn't the totalPointsEarned get too high too fast and exceed type limits (possibly even with long)? Even if they didn't in this case, what if we were summing up multiple Bill Gates' bank accounts.

I initially tried doing this:
averageOverallScore = (averageOverallScore + thisSessionsScore) / 2

...and quickly discovered that this doesn't work mathematically.

How do other people do this? I assume it's somewhat common a task.


Measure The 'Average RMS Power'
I would like to measure the 'Average RMS Power' (in VB) of
of 'Wav Mpeg Layer 3 Format' files before they are Played?
Any One ?

Trying To Get The Average In The Loop....dunno How
Trying to display the average in the loop but dunno how....when looped I can only get the first data of the .txt file, cant get all 5 to get an average

My code looks like this:

Option Explicit

Private Sub Form_Activate()

Dim age1, age2, age3, age4, age5, sal1, sal2, sal3, sal4, sal5 As Integer
Dim nam1, nam2, nam3, nam4, nam5 As String
Dim mthly1, mthly2, mthly3, mthly4, mthly5, avgage, avgmthly, avgsal As Single

Picreport.Print Tab(15); "NAME"; Tab(30); "SALARY"; Tab(60); "AGE"; Tab(80); "MONTHLY"
Picreport.Print ""

Open "a:sample.txt" For Input As #1

Do While Not EOF(1)

Input #1, nam1, sal1, age1

mthly1 = sal1 / 12
mthly2 = sal2 / 12
mthly3 = sal3 / 12
mthly4 = sal4 / 12
mthly5 = sal5 / 12

avgage = (age1 + age2 + age3 + age4 + age5) / 5
avgsal = (sal1) / 5
avgmthly = (mthly1 + mthly2 + mthly3 + mthly4 + mthly5) / 5

Picreport.Print Tab(15); nam1, _
; Tab(30); FormatCurrency(sal1, 2), _
; Tab(60); Format(age1), _
; Tab(80); FormatCurrency(mthly1)

Picreport.Print ""
Picreport.Print Tab(10); "AVERAGES:"; Tab(30); FormatCurrency(avgsal, 2); Tab(60); FormatNumber(avgage, 1); Tab(80); FormatCurrency(avgmthly, 2)

Close #1

End Sub

MY "sample.txt" looks like this
"Bob", 200000, 29
"John", 80000, 26
"Ray", 4000000, 30
"Chris", 150000, 20
"joe", 600000, 19

thank YOU ,

For Loop For Average Number
I am having trouble getting my average button too work using a for loop. Help would be appreciated thx! I have 5 input text boxes which i figure whats the largest number the user inputted and whats the smallest which both work, but my average button keeps giving me 1.
Dim x(10) As Double

Private Sub Command1_Click()
For i = 0 To 4
x(i + 1) = Val(Text1(i))
Next i
Largest = x(1)
For i = 1 To 5
If (x(i) > Largest) Then
Largest = x(i)
End If
Next i
Label1 = Largest
End Sub

Private Sub Command2_Click()
For i = 0 To 4
x(i + 1) = Val(Text1(i))
Next i
Smallest = x(1)
For i = 1 To 5

If (x(i) < Smallest) Then
Smallest = x(i)
End If
Next i
Label1 = Smallest
End Sub

Private Sub Command3_Click() '<----------------average button
i = 0
For i = 0 To 4
i = i + 1
j = i / 5
Next i
Label1 = j

Copyright © 2005-08, All rights reserved