 Publishers of the world’s most comprehensive and up-to-date Excel tutorials ## Excel tutorials

The only Excel books you will ever need

• The only constantly updated Excel 365 titles more…
• Thoroughly covers Power Pivot and Power Query more…
• Covers business intelligence and OLAP features more…
• Simple, easy, understandable, 2-page lessons more…
• Available as both printed books and e-Books more…
• Used by schools, colleges and universities  more…
• Available for 365, 2019, 2016 and Mac versions more…

# Calculating the original number after a percentage increase

## Gross Margin and Markup

If you’ve arrived at this page it is likely that you are working in a sales environment. For that reason I’ll start this article with a brief explanation of Gross Profit and Markup (excerpted from my Expert Skills book).

Newcomers to the retail business often confuse mark-up with gross profit. In general business, gross profit is the more widely accepted metric when discussing profits.

### Here’s an example of mark up:

Bob buys a watch for \$500 and marks it up by 50%. This means that he adds 50% to the cost price and sells it for \$750. In this case, the formula is:

Selling Price = Cost Price * (1+MarkUp%)

The Excel formula that could be used is:

=500*(1+.5)

If you use Excel’s percentage format a value of 50% is stored by Excel as 0.5. For this reason you could use the following formula (which is exactly the same).

=500*(1+50%)

And you could also do this.

=500*(100%+50%)

Or even.

=500*150%

All four formulas are doing exactly the same thing. (The percentage format has the potential to cause great confusion amongst Excel beginners).

### Here’s an example of Gross Profit:

Bill buys a hard disk for \$100. His company needs to make 50% gross profit on all goods sold. This means that they need to sell the hard drive for \$200, making 50% of the selling price (\$100) in profit.

In this example, the formula is:

Selling Price = Cost Price/(1-Gross Profit%)

The Excel formula that would be used for this example is.

=100/(1-0.5)

In this example that would result in a selling price of \$200.

You can see from the above example that a gross profit of 50% is the same as a mark-up of 100%.

Terminology
I use the terms Gross Profit when discussing both cash amounts and percentages.

There’s a lot of controversy about correct terminology. Some purists would argue that the correct terms are Gross Margin (or simply Margin) for Percentage Gross Profit, and Gross Profit for a Gross Profit cash value.

## Calculating the original number after a percentage increase

The problem you are probably struggling with is this:

Bob sold his watch for \$750 after marking it up by 50%. What was Bob’s cost price?

Many student’s first instinct is to reduce the selling price by 50% and use the formula:

=750*50%

Of course that results in \$325 which is the wrong answer (though it would be the right answer if 50% was the gross profit percentage rather than a mark-up percentage).

Let’s look again at the formula Bob used to calculate his selling price:

=500*(1+50%)

Bob had multiplied the cost price by (1+50%) to arrive at his selling price.

This means that the selling price of the watch is now 150% of the cost price. To return to the cost price you’d have to multiply by 100/150.

The formula that is needed is thus:

Cost Price = Selling Price * 1/(1+50%)

In the example of Bob’s watch the Excel formula would be:

=750*1/(1+50%)

## Rounding the result

In the simple example above rounding isn’t an issue. When you work with real-world data rounding can become an issue. Consider this calculation:

I buy an item for \$123.45 and mark it up by 50%.

This results in a selling price of: \$185.175. You’d probably want to sell the item for \$185.18.

Excel’s ROUND function can solve this.

Here’s a formula you could use to solve the problem above.

=ROUND(123.45*1.5,2)

This results in \$185.18.

## Recent Articles

### 2 Responses

1. dan says:

Hi, this is very helpful but, not exactly what I am looking to solve.
Here is the real world example I am trying to figure out:
I’ll keep the numbers simple to make it as easy as possible to build the Excel formula (and an example where you know the answer .
You have TWO pieces of information (column A and column B).
Column A your is current price of stock, \$20,
Column B is the other piece of info you are provided and is “% off peak price”, in this example it is -80%.
Column C is where the formula needs to be built. In this example you know that if you are at \$20 and are 80% off peak you need to a 400% % increase to get back to your starting price (in this case we know that is \$100, because to grow from \$20 to \$100 is a 400% gain.
I am trying to build a formula for column C that uses the actual data in column A (current stock price in \$) and in Column B (the % off peak stock price).
It is probably a simple solution but, I simply cannot figure out how to build it in Excel (and I was a Math major!).
Thank you,
Dan

1. Mike Smart says:

Hi Dan

If you only want to cater for negative percentages then (assuming your values are in row 1) the formula you need is:

=A1/(1+B1)

If you wanted to cater for positive percentages as well you would need to use an IF function with B1>0 as the logical test.

## Which tutorial do you need?   ### Which Excel Essential Skills version do you need to learn?

#### Excel 2016 for Mac 