Average of non-null text boxes on form?

[joebox888]

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

 

[Gammachaser]

You have a couple of problems here... first, yes you need to call NewAvg at some point to get intC, but that is the least of your worries.

If there are any null values in .x1, .x2, etc (what are those dots related to anyway? Is there a missing 'With' statement?) the entire result will be null because:

1 + 2 + 3 + Null = Null

So, you have to eliminate all of the null values from the equation or you will always get null.

Try something like:

Function NonNullAvg () as Single
Dim intC As Integer
Dim ctrl As Control
intC = 0 ' <-------- You don't need this. intC is not persistent.

For Each ctrl In Me.Controls
If ctrl.Tag = "T" AND Not IsNull(ctrl) Then
  intC = intC + 1
  NonNullAvg = NonNullAvg + ctrl
End If
Next
NonNullAvg = NonNullAvg / intC
End Function

Then use:

.Avg = NonNullAvg

Again, assuming that the '.' is referring to a valid With statement.

Another problem is 'What if the user enters text in the text box?' Is this already accounted for? (there are ways to prevent it.) If not, there will be an error when you try to add the value. Even if the user types a blank space, that will screw things up, because then the control is no longer null, but it does not have a numeric value. And if you use val(ctrl) and it is null, that is another error.

Another question: Is 0 a ligitimate value (i.e. do you want to count it for averaging purposes)? Or is it considered null?

Unless you have accounted for all of this in each control, You may have to use something like:

Function NonNullAvg () as Single
Dim intC As Integer
Dim ctrl As Control
intC = 0 ' <-------- You don't need this. intC is not persistent.

For Each ctrl In Me.Controls
  Select Case ctrl.Tag
    Case "T"
       Select Case Trim(Nz(ctrl,"")) = ""
         Case False
           intC = intC + 1
           NonNullAvg = NonNullAvg + ctrl
       End Select
  End Select
Next
NonNullAvg = NonNullAvg / intC
End Function

And there are still a few things that could go wrong based on the way your controls are set up (and what the answers to the previous questions are) and what the users are allowed to input. (General Rule: If a user is allowed to do something, no matter how stupid it sounds, someone will eventually do it and you have to plan for it.)

Obviously more info is needed to fully answer the question. I hope this helps get you headed in the right direction.

 

[joebox888]

Thanks for the help Gamma!
Regarding the user input q, I have an Input-mask(00.00) and Required tags setup however that will now have to change because all the boxes now don't have to be filled..
The dots are a resultant of converting a Macros to VB code, Thought it was normal.. they are deleted now! Zero would be regarded as null here and if text box value is 00.00 then i dont want that box to be counted.
Ive just started implementing your code;
you say "Then use"

AVG=NotNullAVG

where should this go?
Straight away i get the error msg: "object doesnt support this property or method." and this line is highlighted

Select Case Trim(Nz(ctrl, "")) = ""