Solved

Percent of change calculation VB.NET

Posted on 2010-11-12
8
1,820 Views
Last Modified: 2012-05-10
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
Comment
Question by:shelbyinfotech
  • 6
  • 2
8 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34123126
You just want
CalcPercent = NewVal/OldVal * 100
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34123132
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34123174
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34123183
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:shelbyinfotech
ID: 34123269
Case 1 is not giving me -5%
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34123328
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
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34123387
You do have a formatting issue though.
Try this
CalcPercent = Round((NewVal - OldVal) / OldVal * 100,2)
0
 

Author Closing Comment

by:shelbyinfotech
ID: 34123795
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now