[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Compare percentages and take action??

Posted on 2004-03-29
10
Medium Priority
?
345 Views
Last Modified: 2013-12-25
I am creating a calculator to test gross margin percents.  The calculator calculates and displays the percentages just fine.  Now I am trying to add an image to designate wither a margin is approved or not.  The images are only working some of the time and other times they are not working like I would expect.  

Simply put - This is what I'm after:
Margin >=20% - Smiley Pic
Margin <20% AND >10% Neutral Smiley Pic
Margin <=10% Frowning Pic

Seems to work fine when things are above 20%, but in the middle or below 10% it's hit or miss.  I think it may have something to do with rounding or else I am not comparing the decimal/percents correctly.

Here is part of my code:

'Formulas
        decTotalNet = (decSell * intQty)
        decTotalGMD = (intQty * decSell) - (intQty * decCost)
        decTotalCost = (decCost * intQty)
        'Gross Margin Percent Formula = (Selling Price - Total Cost) ÷ Selling Price
        Try
            decTotalGMP = (decTotalNet - decTotalCost) / decTotalNet
        Catch
            MessageBox.Show("The sell price and sell quantity values must both be greater than zero.  Please try again.", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        'Calculate and display the extended amounts
        lblExtendedSell.Visible = True
        lblExtendedSellValue.Text = FormatCurrency(decSell * intQty, 2)
        lblExtendedCost.Visible = True
        lblExtendedCostValue.Text = FormatCurrency(decCost * intQty, 2)
        lblTotalGMD.Visible = True
        lblTotalGMDValue.Text = FormatCurrency(decTotalGMD, 2)
        lblTotalGMP.Visible = True
        lblTotalGMPValue.Text = FormatPercent(decTotalGMP, 2)

        'Calculate the margins at the various % increase & decrease levels
         'Here I go through calculations at 3,5,7,10,12, and 15 %.  I didn't include them all as they are basically the same.

        'Markup Percent formula is Selling Price = Total Cost x (1 + Mark-Up Percent)
        'Example: if you want a mark-up of 27%
        'Would be Selling Price = $2.00 x (1 + 0.27)

        '3% Increase
        Dim dec3Sell As Decimal
        Dim dec3GMD As Decimal
        Dim dec3GMP As Decimal

        dec3Sell = decTotalNet * (1 + 0.03)
        dec3GMD = dec3Sell - decTotalCost
        dec3GMP = (dec3Sell - decTotalCost) / dec3Sell
        lbl3GMP.Text = FormatPercent(dec3GMP, 2)
        lbl3GMD.Text = FormatCurrency(dec3GMD, 2)
        lbl3Sell.Text = FormatCurrency(dec3Sell, 2)
        'Test for good margins and display symbol
        If dec3GMP >= 0.2 Then
            pic3.Image = picSmile.Image
            pic3.Visible = True
        ElseIf dec3GMP <= 0.1 Then
            pic3.Image = picFrown.Image
            pic3.Visible = True
        ElseIf dec3GMP < 0.2 And dec3GMP > 0.1 Then
            pic3.Image = picNtrl.Image
            pic3.Visible = True
        Else
            pic3.Visible = False
        End If

        '5% Increase
        Dim dec5Sell As Decimal
        Dim dec5GMD As Decimal
        Dim dec5GMP As Decimal
        dec5Sell = decTotalNet * (1 + 0.05)
        dec5GMD = dec5Sell - decTotalCost
        dec5GMP = (dec5Sell - decTotalCost) / dec5Sell
        lbl5GMP.Text = FormatPercent(dec5GMP, 2)
        lbl5GMD.Text = FormatCurrency(dec5GMD, 2)
        lbl5Sell.Text = FormatCurrency(dec5Sell, 2)
        'Test for good margins and display symbol
        If dec5GMP >= 0.2 Then
            pic5.Image = picSmile.Image
            pic5.Visible = True
        ElseIf dec5GMP <= 0.1 Then
            pic5.Image = picFrown.Image
            pic5.Visible = True
        ElseIf dec5GMP < 0.2 And dec5GMP > 0.1 Then
            pic5.Image = picNtrl.Image
            pic5.Visible = True
        Else
            pic5.Visible = False
        End If

------
Sample of the weird output:

I enter $25 as the sell, $23 as the cost, with a qty of 1.  I get:

3% increase = GM 10.68% - No Pic (should be neutral)
5% increase = GM 12.38% - Neutral Pic (why did it work here??)
7% increase = GM 14.02% - No Pic (should be neutral)

Help!  What am I missing?  Thanks in advance.
0
Comment
Question by:EPA
[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
  • 5
  • 5
10 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 10709780
Hi EPA,
I suggest you replace your If statements to a Select Statement

Select Case decNGMP
Case Is >= 0.2
      picN.Image = picSimile.Image
Case IS <= 0.1
      picN.Image = picSmile.Frown
Case Else 'No need to complicate. If it is not one of the others, then go here
      pinN.Image = picNtrl.Image
End Select

Dabas
0
 

Author Comment

by:EPA
ID: 10709849
Dabas,

Thanks for the suggestion.  I tried this like you said:

Select Case dec3GMP
            Case Is >= 0.2
                pic3.Image = picSmile.Image
            Case Is <= 0.1
                pic3.Image = picFrown.Image
            Case Else 'No need to complicate. If it is not one of the others, then go here
                pic3.Image = picNtrl.Image
        End Select

I only tested it for the 3% markup and it doesn't work.  The 3% comes out at 10.68% which should trigger a picNtrl.Image, but it's blank.  I don't understand what is going wrong.  Even if it didn't understand the other two ot should at least take the value of the Case Else right?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10709920
EPA,
Do you have a Try Catch End Try statement that you did not include in your code above for the whole procedure?
Seems like an error is triggering which does not allow the code to be reached.

Have you tried setting a breakpoint and single stepping through the code?

Dabas
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:EPA
ID: 10710017
Dabas,

Here is the code from start to 3% (with your select case suggestion), the rest is the same with it just incrementing fro 3 to 5 to 7, etc

  'Declare the variables
        Dim decSell As Decimal
        Dim decCost As Decimal
        Dim intQty As Integer
        Dim decTotalGMD As Decimal
        Dim decTotalNet As Decimal
        Dim decTotalCost As Decimal
        Dim decTotalGMP As Decimal

        'Test the input box to make sure the user has eneter valid values
        Try
            decSell = CDec(txtSell.Text)
        Catch 'Display a message is valid values have not been entered in the text boxes
            MessageBox.Show("You have entered an invalid sell amount.  Please try again.", "Invalid Data Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtSell.Focus()
            Exit Sub 'Stop code execution if error is found
        End Try

        Try
            decCost = CDec(txtCost.Text)
        Catch
            MessageBox.Show("You have entered an invalid cost amount.  Please try again.", "Invalid Data Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtCost.Focus()
            Exit Sub
        End Try

        Try
            intQty = CInt(txtQty.Text)
        Catch
            MessageBox.Show("You have entered an invalid quantity.  Please try again.", "Invalid Data Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            txtQty.Focus()
            Exit Sub
        End Try

        'Formulas
        decTotalNet = (decSell * intQty)
        decTotalGMD = (intQty * decSell) - (intQty * decCost)
        decTotalCost = (decCost * intQty)
        'Gross Margin Percent = (Selling Price - Total Cost) ÷ Selling Price
        Try
            decTotalGMP = (decTotalNet - decTotalCost) / decTotalNet
        Catch
            MessageBox.Show("The sell price and sell quantity values must both be greater than zero.  Please try again.", "Invalid Entry", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try

        'Calculate and display the extended amounts
        lblExtendedSell.Visible = True
        lblExtendedSellValue.Text = FormatCurrency(decSell * intQty, 2)
        lblExtendedCost.Visible = True
        lblExtendedCostValue.Text = FormatCurrency(decCost * intQty, 2)
        lblTotalGMD.Visible = True
        lblTotalGMDValue.Text = FormatCurrency(decTotalGMD, 2)
        lblTotalGMP.Visible = True
        lblTotalGMPValue.Text = FormatPercent(decTotalGMP, 2)

        'Calculate the margins at the various % increase & decrease levels

        'Markup Percent formula is Selling Price = Total Cost x (1 + Mark-Up Percent)
        'Example: if you want a mark-up of 27%
        'Would be Selling Price = $2.00 x (1 + 0.27)

        '3% Increase
        Dim dec3Sell As Decimal
        Dim dec3GMD As Decimal
        Dim dec3GMP As Decimal

        dec3Sell = decTotalNet * (1 + 0.03)
        dec3GMD = dec3Sell - decTotalCost
        dec3GMP = (dec3Sell - decTotalCost) / dec3Sell
        lbl3GMP.Text = FormatPercent(dec3GMP, 2)
        lbl3GMD.Text = FormatCurrency(dec3GMD, 2)
        lbl3Sell.Text = FormatCurrency(dec3Sell, 2)
        'Test for good margins and display symbol

        Select Case dec3GMP
            Case Is >= 0.2
                pic3.Image = picSmile.Image
            Case Is <= 0.1
                pic3.Image = picFrown.Image
            Case Else 'No need to complicate. If it is not one of the others, then go here
                pic3.Image = picNtrl.Image
        End Select

----

What's weird is how it works sometimes and not others.  The 5% seemed to work just find, but not the 3%.  I did set a breakpoint at the 3% mark and walked thru it.  The values look like they make it all the way through and it looks like the program is following the right path on my if statements, but the end result is incorrect.

Appreciate the help.  EPA
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10710080
EPA,
Just tried the following on my system. (Replaced setting of images with console Writelines)

Code:

    Const decTotalNet As Decimal = 25
    Const decTotalCost As Decimal = 23
    Private Sub Button5_Click_2(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        SetImage(0.03, PictureBox1)
        SetImage(0.05, PictureBox1)
        SetImage(0.07, PictureBox1)
    End Sub
    Private Sub SetImage(ByVal Percent As Single, ByVal Pic As PictureBox)
        Dim decSell As Decimal
        Dim decGMD As Decimal
        Dim decGMP As Decimal
        decSell = decTotalNet * (1.0 + Percent)
        decGMD = decSell - decTotalCost
        decGMP = (decSell - decTotalCost) / decSell
        Console.WriteLine("decGMP = " & decGMP.ToString & " Percent = " & Format(Percent, "Percent"))
        If decGMP >= 0.2 Then
            Console.WriteLine("Smile")
            Pic.Visible = True
        ElseIf decGMP <= 0.1 Then
            Console.WriteLine("Frown")
            Pic.Visible = True
        ElseIf decGMP < 0.2 And decGMP > 0.1 Then
            Console.WriteLine("Neutral")
            Pic.Visible = True
        Else
            Pic.Visible = False
        End If
    End Sub

Result:
decGMP = 0.1067961159233595622569326225 Percent = 3.00%
Neutral
decGMP = 0.1238095244312485437765254143 Percent = 5.00%
Neutral
decGMP = 0.1401869161273317144935215824 Percent = 7.00%
Neutral

Dabas
0
 
LVL 27

Expert Comment

by:Dabas
ID: 10710141
EPA,
>                 pic3.Image = picFrown.Image

My guess is that the above line causes an exception.

Dabas
0
 

Author Comment

by:EPA
ID: 10710321
Interesting...

So with your results we go all neutrals which is correct

Result:
decGMP = 0.1067961159233595622569326225 Percent = 3.00%
decGMP = 0.1238095244312485437765254143 Percent = 5.00%
decGMP = 0.1401869161273317144935215824 Percent = 7.00%

Why would pic3.Image = picFrown.Image cause an error, but not pic3.Image = picSmile.Image ?  I double checked all the the image names and the pic boxes.  I can't see any spelling errors.

In mine neither 3% or 7% display a pic, but 5% does correctly display the neutral pic

Here is the code for 5 & 7.  I can't see anything different can you?

  '5% Increase
        Dim dec5Sell As Decimal
        Dim dec5GMD As Decimal
        Dim dec5GMP As Decimal
        dec5Sell = decTotalNet * (1 + 0.05)
        dec5GMD = dec5Sell - decTotalCost
        dec5GMP = (dec5Sell - decTotalCost) / dec5Sell
        lbl5GMP.Text = FormatPercent(dec5GMP, 2)
        lbl5GMD.Text = FormatCurrency(dec5GMD, 2)
        lbl5Sell.Text = FormatCurrency(dec5Sell, 2)
        'Test for good margins and display symbol
        If dec5GMP >= 0.2 Then
            pic5.Image = picSmile.Image
            pic5.Visible = True
        ElseIf dec5GMP <= 0.1 Then
            pic5.Image = picFrown.Image
            pic5.Visible = True
        ElseIf dec5GMP < 0.2 And dec5GMP > 0.1 Then
            pic5.Image = picNtrl.Image
            pic5.Visible = True
        Else
            pic5.Visible = False
        End If

        '7% Increase
        Dim dec7Sell As Decimal
        Dim dec7GMD As Decimal
        Dim dec7GMP As Decimal
        dec7Sell = decTotalNet * (1 + 0.07)
        dec7GMD = dec7Sell - decTotalCost
        dec7GMP = (dec7Sell - decTotalCost) / dec7Sell
        lbl7GMP.Text = FormatPercent(dec7GMP, 2)
        lbl7GMD.Text = FormatCurrency(dec7GMD, 2)
        lbl7Sell.Text = FormatCurrency(dec7Sell, 2)
        'Test for good margins and display symbol
        If dec7GMP >= 0.2 Then
            pic7.Image = picSmile.Image
            pic7.Visible = True
        ElseIf dec3GMP <= 0.1 Then
            pic7.Image = picFrown.Image
            pic7.Visible = True
        Else
            pic7.Visible = False
        End If
0
 

Author Comment

by:EPA
ID: 10710358
Okay I found one issue with the 7%... should have been:  ElseIf dec7GMP <= 0.1 Then
0
 
LVL 27

Accepted Solution

by:
Dabas earned 500 total points
ID: 10710387
EPA,
I suggest you rewrite using my subroutine.
That way you know you will not make an error like that.
Also check pic5 in design time. Are you able to display the Neutral image on it?

Also add Option Explicit On to the top of your module.

Have you tried single stepping as suggested earlier?

Dabas
0
 

Author Comment

by:EPA
ID: 10710476
Dabas,

Thanks for the help.  I added the Option Explicit like you suggested and single stepped through the code I believe I have found the errors.  I was going to rewrite using your subroutine, but I've got mine going now.  The cod for 7% worked great so after tweaking it I just used it for the rest switching out the #'s where needed.  

I appreciate all you efforts.  Points coming now.  EPA
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

656 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