Share via

Applying a formula to a spreadsheet to calculate gross margin % for items where both List Price and Cost are known

Anonymous
2011-03-28T21:36:41+00:00

How do I create a formula that expresses a gross margin percentage based upon either an items cost or list price?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-03-28T21:54:15+00:00

    Let's say the cost price is in A2 and the list price in B2. Use this formula:

    =B2/A2-1

    and format the cell with the formula as a percentage.

    1 person found this answer helpful.
    0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-03-12T03:03:12+00:00

    (B2-A2)/B2 as a % = Gross Margin

    Then what HansV wrote in response to your fixed GM calculation

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2011-03-28T22:28:08+00:00

    Hans,

    Thanks for your help.  I realized I did not pose the original question correctly.  I know cost and list, but I want to apply a formula that will give me in dollars a specific gross margin percent when applied, in this case 25% gross margin.  So, cost is say $125.00, and I want to apply a 25% gross margin to all cells in the cost column.  How do I do that via a formula?  Thanks very much.

    0 comments
  3. HansV 462.6K Reputation points MVP Volunteer Moderator
    2011-03-28T22:20:27+00:00

    Select the cell with the formula.

    Either click the % button on the Formatting toolbar, or select Format | Cells..., activate the Number tab (if necessary) and select the Percentage category.

    0 comments
  4. Anonymous
    2011-03-28T22:14:37+00:00

    Thanks, I understand the A2 cell B2 cell, but I don't know how to formulate the answer as a percentage?

    0 comments