Percentage difference

[Bob Holdford]

Hi guys,

Not exactly great at formulas, so any help would be appreciated.

Excel 2207

In cell k2 and below are buying prices. In cell j2 and below are selling
prices. I just need to know the 'mark-up' percentage. Also, would be a
great advantage for the formula to work the other way around....showing
'loss' as well !!!!!!!!!!!!

Have tried:

=2*ABS(J2-K2) / (J2 + K2)
and
=(J2-K2)/J2


and several other variations, but to no avail.

Not sure whether I am needing 'relative' or 'absolute' either!!


Thanks in advance

Bob

 

[Claus Busch]

Hi Bob,

Am Sun, 30 Oct 2011 14:38:55 -0000 schrieb Bob Holdford:

In cell k2 and below are buying prices. In cell j2 and below are selling
prices. I just need to know the 'mark-up' percentage. Also, would be a
great advantage for the formula to work the other way around....showing
'loss' as well !!!!!!!!!!!!

=1-(K2/J2)


Regards
Claus Busch

 

[Bob Holdford]

Thanks for that.

Gives same results as my, much longer, formulas do


Cheers
Bob.

"Claus Busch" wrote in message
Hi Bob,

Am Sun, 30 Oct 2011 14:38:55 -0000 schrieb Bob Holdford:

In cell k2 and below are buying prices. In cell j2 and below are selling
prices. I just need to know the 'mark-up' percentage. Also, would be a
great advantage for the formula to work the other way around....showing
'loss' as well !!!!!!!!!!!!

=1-(K2/J2)


Regards
Claus Busch

 

[joeu2004]

Thanks for that Gives same results as my, much longer, formulas do

Sounds like Claus's formula give you want you want. But that formula is

Have tried: [....]
=(J2-K2)/J2
and several other variations, but to no avail.

"To no availe" suggests to me that (J2-K2)/J2 does not give the result you
are looking for.

And for good reason, if I understand your requirements correctly.

You wrote previously:

In cell k2 and below are buying prices. In cell j2
and below are selling prices. I just need to know
the 'mark-up' percentage. Also, would be a great advantage
for the formula to work the other way around....showing 'loss' as well

AFAIK, the "mark up" is the percentage increase (or decrease) of the retail
price (J2) over the wholesale price or cost (K2). That would be:

=J2/K2-1

formatted as Percentage, which is equivalent to (J2-K2)/K2. Note the
difference in the demoninator.

As for "showing loss as well", that is unclear.

If you mean the loss due to selling at a price less than the cost, J2/K2-1
will do that, too.

But if you mean that you want to determine what percentage that the cost is
less than the retail price (not really the "mark down" percentage, as I
understand the use of that term in the retail industry), that would be:

=K2/J2-1

formatted as Percentage, which is equivalent to (K2-J2)/J2. Note the
difference in order in the numerator.

 

[Steve]

Bob:

Mark up percentage is calculated two different ways depending on
whether you want to know the percentage of mark up from cost or the
percentage of profit based on selling price.

For instance, if an item cost you $6.00 and you sell it for $10.00, to
get the selling price from cost you need to "mark-up" the cost by
1.66667. On the other hand, if you want to find the amount of profit
made on an item bases on selling price, then the $6.00 cost is 60
percent of the selling price which yields a true profit margin of 40
percent ($10.00 minus 40 percent is $6.00).

So, in order for any formula to give you the results you need, the
first order of business is to determine whether you want to determine
your gross profit based on cost or selling price.
--
Steve Spence
AMSOIL - The "Once A Year" Oil Change
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)
| Hi guys,
|
| Not exactly great at formulas, so any help would be appreciated.
|
| Excel 2207
|
| In cell k2 and below are buying prices. In cell j2 and below are
selling
| prices. I just need to know the 'mark-up' percentage. Also, would
be a
| great advantage for the formula to work the other way
around....showing
| 'loss' as well !!!!!!!!!!!!
|
| Have tried:
|
| =2*ABS(J2-K2) / (J2 + K2)
| and
| =(J2-K2)/J2
|
|
| and several other variations, but to no avail.
|
| Not sure whether I am needing 'relative' or 'absolute' either!!
|
|
| Thanks in advance
|
| Bob
|
|
|
|
|

 

[Steve]

To find mark up based on cost, it would be =(J2/K2)-1. A positive
decimal result would signify a positive gross profit while a negative
decimal result would signify a loss on the item.

To find mark up based on selling price, it would be =1-(K2/J2).
Again, a positive number would show a positive gross profit while a
negative number would show a loss on the item.

--
Steve Spence
AMSOIL - The "Once A Year" Oil Change
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)
| Hi guys,
|
| Not exactly great at formulas, so any help would be appreciated.
|
| Excel 2207
|
| In cell k2 and below are buying prices. In cell j2 and below are
selling
| prices. I just need to know the 'mark-up' percentage. Also, would
be a
| great advantage for the formula to work the other way
around....showing
| 'loss' as well !!!!!!!!!!!!
|
| Have tried:
|
| =2*ABS(J2-K2) / (J2 + K2)
| and
| =(J2-K2)/J2
|
|
| and several other variations, but to no avail.
|
| Not sure whether I am needing 'relative' or 'absolute' either!!
|
|
| Thanks in advance
|
| Bob
|
|
|
|
|

 

[Bob Holdford]

Guys, you are all awesome. I have learnt a lot from this thread.

Thanks


Bob.




"Steve" wrote in message
To find mark up based on cost, it would be =(J2/K2)-1. A positive
decimal result would signify a positive gross profit while a negative
decimal result would signify a loss on the item.

To find mark up based on selling price, it would be =1-(K2/J2).
Again, a positive number would show a positive gross profit while a
negative number would show a loss on the item.

--
Steve Spence
AMSOIL - The "Once A Year" Oil Change
URL: http://synthetic-oil-tech.com/1690163
Email: (e-mail address removed)
| Hi guys,
|
| Not exactly great at formulas, so any help would be appreciated.
|
| Excel 2207
|
| In cell k2 and below are buying prices. In cell j2 and below are
selling
| prices. I just need to know the 'mark-up' percentage. Also, would
be a
| great advantage for the formula to work the other way
around....showing
| 'loss' as well !!!!!!!!!!!!
|
| Have tried:
|
| =2*ABS(J2-K2) / (J2 + K2)
| and
| =(J2-K2)/J2
|
|
| and several other variations, but to no avail.
|
| Not sure whether I am needing 'relative' or 'absolute' either!!
|
|
| Thanks in advance
|
| Bob
|
|
|
|
|