average of non-null text boxes

[joebox8]

Hi there!...I have a fairly easy problem(i think!), Just wont work..
Up to now my basic average system was just fine.. I have 8 text boxes that HAD to be filled before any calculations could begin!

.AVG = (.x1 + .x2 + .x3 + .x4 + .x5 + .x6 + .x7 + .x8) / 8

Now they don't all have to be filled so how could i get the average of only the text boxes that have been filled??!

Does anyone know if this this should work??
Ive tagged all my input text boxes with a "T" and put this code on the on-current property of the form.
this code simply counts all the empty textboxes from my tagged group and that value is then 'intC'

Private Sub newAVG()
Dim intC As Integer
Dim ctrl As Control
intC = 0

For Each ctrl In Me.Controls
If ctrl.Tag = "T" Then
If IsNull(ctrl) Then
intC = intC + 1
End If
End If
Next
End Sub



Then for my average function I have
.AVG = (.x1 + .x2 + .x3 + .x4 + .x5 + .x6 + .x7 + .x8) / (8 - intC)

Can i just subtract intC from the total number like that?
Am i wording it wrong or should i be calling newAVG somewhere else? If so please help..
Thanks
Thanks

 

[GinaWhipp]

EDIT: Forgot...

WELCOME to the forum!

Try using Nz()...

.AVG = (Nz(.x1, 0) + Nz(.x2, 0) + Nz(.x3, 0) + Nz(.x4, 0) + Nz(.x5, 0) + Nz(.x6, 0) + Nz(.x7, 0) + Nz(.x8, 0)) / 8

Then no need for intC.

 

[joebox8]

hey thanks Gina,
Still no joy...

.AVG = (Nz(.x1, 0) + Nz(.x2, 0) + Nz(.x3, 0) + Nz(.x4, 0) + Nz(.x5, 0) + Nz(.x6, 0) + Nz(.x7, 0) + Nz(.x8, 0)) / 8

At the moment i have an input mask of "00.00" as that's the format the numbers must be entered, Would the 00.00 be messing that up?

Also the above code still divides by 8 however what i need is when no data is entered in the box then its not counted towards the calculation.
So if x8 was empty then the calculation would automatically be (x1+x2+x3+x4+x5+x6+x7)/7

Thanks

 

[plog]

In your code, instead of looking for nulls, I'd look for non-null values. When one is found, add it to a running sum and also increment a counter that counts how many non-nulls you have. Then at the end you divide the running sum by the non-null counter--provided the non-null counter is greater than zero.

Here's some psuedo code:

RunningSum=0
NonNulls=0
Avg="N/A"
 
For Every Input
     If Input Is Not Null
        RunningSum=RunningSum + Input.Value
        NonNulls=NonNulls + 1
 
if NonNulls>0 Then Avg=RunningSum/NonNulls

 

[GinaWhipp]

Oh, I misunderstood... I thought you wanted to divide by 8 no matter what. I see Plog has given you an idea. Where you able to implement that?

 

[joebox8]

Thanks guys, Just got that there! where would i place that code and how would i phrase it in vba? Im very new to access!

 

[GinaWhipp]

Are all these fields in one record? If yes, you get get the total from and invisible field and then in you dode use a count function to determine which fields have values >0 to to the division?

Also does this have to show on the form only?