Go Premium for a chance to win a PS4. Enter to Win

x
Solved

Percent of change calculation VB.NET

Posted on 2010-11-12
Medium Priority
2,065 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
``````
PCT.jpg
0
Question by:shelbyinfotech
• 6
• 2
8 Comments

LVL 37

Expert Comment

ID: 34123126
You just want
CalcPercent = NewVal/OldVal * 100
0

LVL 37

Expert Comment

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

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

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

Author Comment

ID: 34123269
Case 1 is not giving me -5%
0

LVL 37

Expert Comment

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

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

Author Closing Comment

ID: 34123795
Thanks
0

Featured Post

Question has a verified solution.

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

Itâ€™s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following â€¦
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeâ€¦
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calculâ€¦
Suggested Courses
Course of the Month11 days, 6 hours left to enroll

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

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