+ Reply to Thread
Results 1 to 6 of 6

Margin Formula

  1. #1
    Registered User
    Join Date
    02-20-2008
    Posts
    2

    Unhappy Margin Formula

    HELP???

    I use a Sharp margin calculator quite often and would like to create a formula to do this in an Excel spreadsheet. Unfortunately I have a limited knowledge of MS Excel. Version is 202

    Cost = $2.44
    Sell= $2.99

    I need to create a formula in order to calculate margin (% of Profit) which should is 18.39%

    Can anyone help me with this?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Margin = 1 - Cost / Sell

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    just to elaborate!
    in cell a1 put cost value 2.44
    in cell b1 put sell value 2.99
    in cell c1 put
    =1-A1/B1
    format cell c1 as % (format/cells/percentage)
    result from your example =18.39%
    n.b. if you wish to include $ sign in cells format cells with values as currency/ $ us(english)
    Last edited by martindwilson; 02-20-2008 at 08:43 PM.

  4. #4
    Registered User
    Join Date
    02-20-2008
    Posts
    2

    Thanks! Still need some help, though

    Thanks shg and Martin!

    I am almost there with what I need.

    Attached is the spreadsheet that has an error with 1 or 2 formulas. Can anyone take a look at it to see what is wrong?
    Attached Files Attached Files

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    The difference between the results in the two formulas is that in the first case the unit cost is calculated as $2.4375, and in the second the cost is entered as $2.44 exactly.

    The first result is the one that is technically correct, but with a difference of 0.08%, it isn't much different.

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    as shg says its coz you cant see the real result of h6/d6
    cell is formated as "$"#,##0.00 giving only 2 decimal places
    change to
    "$"#,##0.000000
    although result is shown as 2.44 excel calculates using the real value which is 2.4375

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1