Gross Margin vs Gross Markup

[Escarabajo]

Why does my Gross Margin markup give me the error #DIV/0! when marked up 100% and when I go over 100% I get a negative number?

	A	B	C	D
1	COST	MU	SELL
2	$18	50%	=A2/(1-B2) = $36.00	Gross Margin
3	$18	50%	=(A3*B3)+A3 = $27.00	Gross Markup
4	$18	100%	=A4/(1-B4) = #DIV/0!	Gross Margin ERROR

What am I missing here?

Gross Margin vs Markup.xlsm
	B	C	D	E	F	G
1
2	Cost	MU	Sell
3	$ 18.00	100%	#DIV/0!	Gross Margin		The type of margin we’re discussing in this case is gross profit margin, which describes the profit that you earn on a product as a percentage of the selling price.
4
5			$ 36.00	Gross Markup		You can think of markup as the extra percentage that you charge your customers (on top of your cost).
Sheet1

Cell Formulas
Range	Formula
D3	D3	=B3/(1-C3)
D5	D5	=(B3*C3)+B3

 

[Joe4]

Note that 100% is really equal to 1.
So in the denominator of that formula, you have (1-B4), which is then:
1-100%
or
1-1
which equals
0

And you cannot divide by zero. That is impossible and will return an error.

If you can explain what number should be returned for this example, and explain how to arrive at that number, we can probably help you write a formula to do what you need.

 

[Escarabajo]

Note that 100% is really equal to 1.
So in the denominator of that formula, you have (1-B4), which is then:
1-100%
or
1-1
which equals
0

And you cannot divide by zero. That is impossible and will return an error.

If you can explain what number should be returned for this example, and explain how to arrive at that number, we can probably help you write a formula to do what you need.

Hi Joe4, so you can have greater than 100% for mark up, but not for margin? Is there a work around for this?

 

[Joe4]

Hi Joe4, so you can have greater than 100% for mark up, but not for margin? Is there a work around for this?

I know nothing about the logic or what these formulas should look like.
I am just giving you a mathematical analysis of your formula and why you get that error (and note you only get that error when B4 is EXACTLY 100%; you would have the same issue if B2 was also 100%).
I have no idea is the formula you came up with is actually the correct one to use.

I suspect that the formula you are trying to use may not be correct.
Where did you get these formulas?
If you came up with them yourself, can you explain the logic in detail?

 

[Escarabajo]

Yea the problem I have run across that I am coming to realize you can have greater than 100% for mark up, but not for margin, this formula calculates for gross profit margin.

Thank you for your input

 

[keef2]

I have don't a lot of work with Cost, Markups and Gross Margin. However, I don't fully understand your formulas nor logic? Maybe this helps or not who knows but please try to explain differently so i can maybe comprehend better. Thanks!

Book1
	A	B	C	D
1	Cost	MU	Sell Price	GM
2	$ 18.00	50%	$ 27.00	33%
3	$ 18.00	50%	$ 27.00	33%
4	$ 18.00	100%	$ 36.00	50%
Sheet3

Cell Formulas
Range	Formula
C2:C4	C2	=A2*(1+B2)
D2:D4	D2	=((C2-A2)/C2)

 

[Escarabajo]

I have don't a lot of work with Cost, Markups and Gross Margin. However, I don't fully understand your formulas nor logic? Maybe this helps or not who knows but please try to explain differently so i can maybe comprehend better. Thanks!

Book1
	A	B	C	D
1	Cost	MU	Sell Price	GM
2	$ 18.00	50%	$ 27.00	33%
3	$ 18.00	50%	$ 27.00	33%
4	$ 18.00	100%	$ 36.00	50%
Sheet3

Cell Formulas
Range	Formula
C2:C4	C2	=A2*(1+B2)
D2:D4	D2	=((C2-A2)/C2)

I followed the following thread where it more or less was explained. Find formula to drive sale price based on entering gross profit percentage (not markup%). We know the cost price

 

[AhoyNC]

Maybe one of these links will help explain why profit margin can't exceed 100%.

Profit Margin - The Personal MBA

Understanding The Difference Between Profit Margin and Markup | Simple Small Business