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

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

Welcome to VBForums :wave:

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.

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

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

Quote:

---

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

---

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 :)