Solved

Compare percentages and take action??

Posted on 2004-03-29
10
321 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
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!

 

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Paint/Redraw window while dragging 16 86
VB6 Compile Compatibility Issue 4 121
Recommendation vb6 to vb.net or others 14 202
Fastest way to find and count same items VB6 16 43
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

740 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