Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Percent of change calculation VB.NET

Posted on 2010-11-12
8
Medium Priority
?
2,009 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

by:shelbyinfotech
ID: 34123795
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 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