# Thread: Finding Average of 3 Numbers excluding zeros

1. ## Finding Average of 3 Numbers excluding zeros

I am creating a application for my personal use. I have three textboxes in the form for inputting 3 numbers. I need to find the average of the three numbers entered in those textboxes.

Below are the scenario's in my application;

One textbox can have value and the other two will be left blank and value in single text box must be displayed as average

Two textbox can contain value and the other can be blank. Average for the Values in two textboxes must be displayed.

All the three text box can have value. Average for all the three values must be found.

I coded for this but it is not working properly. Below is my code for the reference.

Code:
```
Public Sub Calculate_Avg_Rate()
If Val(txtValue1.Text) = "0" Or Len(txtValue1.Text) = 0 Then
If Val(txtRiceB.Text) = "0" Or Len(txtRiceB.Text) = 0 Then
txtAvgRice.Text = Val(txtRiceC.Text)
End If
ElseIf Val(txtValue1.Text) = "0" Or Len(txtValue1.Text) = 0 Then
If Val(txtRiceC.Text) = "0" Or Len(txtRiceC.Text) = 0 Then
txtAvgRice.Text = Val(txtRiceB.Text)
End If
ElseIf Val(txtRiceB.Text) = "0" Or Len(txtRiceB.Text) = 0 Then
If Val(txtRiceC.Text) = "0" Or Len(txtRiceC.Text) = 0 Then
txtAvgRice.Text = Val(txtValue1.Text)
End If
ElseIf Val(txtValue1.Text) = "0" Or Len(txtValue1.Text) = 0 Then
txtAvgRice.Text = Round(((Val(txtRiceB.Text) + Val(txtRiceC.Text)) / 2), 2)
ElseIf Val(txtRiceB.Text) = "0" Or Len(txtRiceB.Text) = 0 Then
txtAvgRice.Text = Round(((Val(txtValue1.Text) + Val(txtRiceC.Text)) / 2), 2)
ElseIf Val(txtRiceC.Text) = "0" Or Len(txtRiceC.Text) = 0 Then
txtAvgRice.Text = Round(((Val(txtValue1.Text) + Val(txtRiceC.Text)) / 2), 2)
Else
txtAvgRice.Text = Round(((Val(txtValue1.Text) + Val(txtRiceB.Text) + Val(txtRiceC.Text)) / 3), 2)
End If
End Sub```
I am calling the above procedure in the fourth textbox got focus event. The fourth textbox will display the average value when it is getting clicked.

When I enter the value in the textboxes, the average is not getting populated. I have also attached the project for reference.

Any help regarding is highly appreciated. Thanks in advance.

--------------------------------------------------------------------------------

Thanks you and Kind Regards, Karthik Venkatraman

2. ## Re: Finding Average of 3 Numbers excluding zeros

You have an issue with your ElseIf structure
Remember ElseIf only executes when the If above it is false so as soon as you run into a true case the tests are complete.
There is also not need for those Or statements as VAL() will return 0 if the text=""

This is also an issue where you are comparing an integer to a string
Code:
`If Val(txtValue1.Text) = "0"`
Should not be any quotes there

3. ## Re: Finding Average of 3 Numbers excluding zeros

Welcome to VBForums

To start with I would recommend thinking about what the code will actually do in various situations. Taking the first part:
Code:
```    If Val(txtValue1.Text) = "0" Or Len(txtValue1.Text) = 0 Then
If Val(txtRiceB.Text) = "0" Or Len(txtRiceB.Text) = 0 Then
txtAvgRice.Text = Val(txtRiceC.Text)
End If
ElseIf Val(txtValue1.Text) = "0" Or Len(txtValue1.Text) = 0 Then```
..If txtValue1 is 0 or empty, the only code that can run is the next If statement, so you will only get a result if txtRiceB is also 0 or empty.

Similar applies to the other code too, but why bother with such a complicated arrangement? Instead of trying to check each possible permutation as a whole, just deal with each part, and do the calculation at the end.

All you really need is two variables (a total, and a count), and three If statements, eg:
Code:
```Dim sngTotal as Single
Dim intCount as Integer
If Not(Val(txtValue1.Text) = 0 Or Len(txtValue1.Text) = 0) Then
sngTotal = sngTotal + Val(txtValue1.Text)
intCount = intCount + 1
End If
...

If intCount > 0 Then
txtAvgRice.Text = Round(sngTotal / intCount ,2)
End If```
Note also that there is no point checking both parts of Val(txtValue1.Text) = 0 Or Len(txtValue1.Text) = 0 , because the Val part by itself will produce the same overall result.

4. ## Re: Finding Average of 3 Numbers excluding zeros

You may want to try somethign more like this
Code:
```If Val(txtValueA.Text) = 0 Then
If Val(txtValueB.Text) = 0 Then
If Val(txtValueC.Text) = 0 Then
'No Valid entries above 0
txtAverage.Text ="0"
Else
txtAverage.Text = Val(txtValueC.Text)
End If
Else
If Val(txtValueC.Text) = 0 Then
'Only b is valid
txtAverage.Text = Val(txtValueB.Text)
Else 'B and C are valid
txtAverage.Text = Round(((Val(txtValueB.Text) + Val(txtValueC.Text)) / 2), 2)
End If
End If
Else
If Val(txtValueB.Text) = 0 Then
If Val(txtValueC.Text) = 0 Then
'Only A is valid
txtAverage.Text = Val(txtValueA.Text)
Else 'Only A and C are valid
txtAverage.Text = Round(((Val(txtValueA.Text) + Val(txtValueC.Text)) / 2), 2)
End If
Else
If Val(txtValueC.Text) = 0 Then
'Only a and b are valid
txtAverage.Text = Round(((Val(txtValueB.Text) + Val(txtValueB.Text)) / 2), 2)
Else ' all are valid
txtAverage.Text = Round(((Val(txtValueA.Text) + Val(txtValueB.Text) + Val(txtValueC.Text)) / 3), 2)
End If
End If
End If```
Edit: Never mind, the post above is a better way to go.
You still may want to look over the If structure in this post though to get an idea of the logic

5. ## Re: Finding Average of 3 Numbers excluding zeros

Thank you for helping me out. I have figured it.

Originally Posted by DataMiser
You may want to try somethign more like this
Code:
```If Val(txtValueA.Text) = 0 Then
If Val(txtValueB.Text) = 0 Then
If Val(txtValueC.Text) = 0 Then
'No Valid entries above 0
txtAverage.Text ="0"
Else
txtAverage.Text = Val(txtValueC.Text)
End If
Else
If Val(txtValueC.Text) = 0 Then
'Only b is valid
txtAverage.Text = Val(txtValueB.Text)
Else 'B and C are valid
txtAverage.Text = Round(((Val(txtValueB.Text) + Val(txtValueC.Text)) / 2), 2)
End If
End If
Else
If Val(txtValueB.Text) = 0 Then
If Val(txtValueC.Text) = 0 Then
'Only A is valid
txtAverage.Text = Val(txtValueA.Text)
Else 'Only A and C are valid
txtAverage.Text = Round(((Val(txtValueA.Text) + Val(txtValueC.Text)) / 2), 2)
End If
Else
If Val(txtValueC.Text) = 0 Then
'Only a and b are valid
txtAverage.Text = Round(((Val(txtValueB.Text) + Val(txtValueB.Text)) / 2), 2)
Else ' all are valid
txtAverage.Text = Round(((Val(txtValueA.Text) + Val(txtValueB.Text) + Val(txtValueC.Text)) / 3), 2)
End If
End If
End If```
Edit: Never mind, the post above is a better way to go.
You still may want to look over the If structure in this post though to get an idea of the logic

6. ## Re: Finding Average of 3 Numbers excluding zeros

You could just use a brutal application of logic:
Code:
```Dim a As Single
Dim b As Single
Dim c As Single
Dim d As Single
a = Val(txtValueA.Text)
b = Val(txtvalueB.Text)
c = Val(txtValueC.Text)
Select Case True
Case a <> 0 And b <> 0 And c <> 0
d = (a + b + c) / 3
Case a <> 0 And b <> 0, a <> 0 And c <> 0, b <> 0 And c <> 0
d = (a + b + c) / 2
Case Else
d = a Or b Or c
End Select
Debug.Print "Average: "; d```
I think that works, but it's getting late here

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•