Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2216
  • Last Modified:

Percent of change calculation VB.NET

Attached is a screenshot of what should seemingly be a simple piece of code. I am attempting to calculate percent of change and avoid a divide by zero.....My code to get around the zero works, however i am getting the wrong answers with what i thought to be the base calculation
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TestCalc()
    End Sub
    Private Function CalcPercent(ByVal NewVal As Integer, ByVal OldVal As Integer) As Decimal

        If NewVal = 0 And OldVal > 0 Then
            CalcPercent = -100
            Exit Function
        End If

        If NewVal > 0 And OldVal = 0 Then
            CalcPercent = 100
            Exit Function
        End If

        If NewVal = 0 And OldVal = 0 Then
            CalcPercent = 0
            Exit Function
        End If


        CalcPercent = (NewVal - OldVal) / OldVal * 100
 

    End Function
    Private Sub TestCalc()
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim myReader As SqlDataReader
        Dim prm As SqlParameter
        Dim NEWVALUE As Integer
        Dim OLDVALUE As Integer
        Dim EXPECTED As String
        Try
            myConnection = New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
            myCommand = New SqlCommand("TestPercentCalcsRTN", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            myConnection.Open()
            myReader = myCommand.ExecuteReader()

            While myReader.Read
                OLDVALUE = myReader("OLDVALUE")
                NEWVALUE = myReader("NEWVALUE")
                EXPECTED = myReader("EXPECTED")
                Response.Write("OLDVALUE : " + Right("_____________" + Str(OLDVALUE), 10) + "   ")
                Response.Write("NEWVALUE : " + Right("_____________" + Str(NEWVALUE), 10) + "   ")
                Response.Write("CALCULATED : " + Right("_____________" + Str(CalcPercent(NEWVALUE, OLDVALUE)), 10))
                Response.Write("____EXPECTED : " + Right("_____________" + EXPECTED, 10) + "   " + "  <BR> ")
            End While

        Catch ex As Exception

            Throw
        Finally

            If Not myReader Is Nothing Then
                myReader.Close()
                myReader = Nothing
            End If

            If Not myConnection Is Nothing Then
                myConnection.Close()
                myConnection.Dispose()
            End If


        End Try
    End Sub

Open in new window

PCT.jpg
0
shelbyinfotech
Asked:
shelbyinfotech
  • 6
  • 2
1 Solution
 
TommySzalapskiCommented:
You just want
CalcPercent = NewVal/OldVal * 100
0
 
TommySzalapskiCommented:
Actually, I think you are a little confused. You are getting the percentage increase currently. Going from 50 to 100 is a 100% increase.
0
 
TommySzalapskiCommented:
Your data is correct, your expectations are wrong.
You don't need
        If NewVal = 0 And OldVal > 0 Then
            CalcPercent = -100
            Exit Function
        End If

It will do that automatically.

        If NewVal > 0 And OldVal = 0 Then
            CalcPercent = 100
            Exit Function
        End If

This shouldn't be 100, it should be infinite. Maybe you should use 99999 or something to make the ouput more indicative of the actual result.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
TommySzalapskiCommented:
So ignore my first post. That will give you what the new value is as a percent of the old value.  Your original formula gives you the percentage change.
0
 
shelbyinfotechAuthor Commented:
Case 1 is not giving me -5%
0
 
TommySzalapskiCommented:
Case 1 should not give you 5% If you want from 100% to 95%, that would be a 5% decrease.

When you go from 105% to 100% you are dropping 5 of the 105 so 5/105 is the correct drop amount. Converted to a percent gives 4.76% and some change, just like it is showing.
0
 
TommySzalapskiCommented:
You do have a formatting issue though.
Try this
CalcPercent = Round((NewVal - OldVal) / OldVal * 100,2)
0
 
shelbyinfotechAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now