Excel calculation required

  johnem 13:20 14 Feb 08
Locked

Trying to work out some accounts details. If cell A1=£1000 and cell B1=£600. Cell C1 =66.67% which is value markup. Cell d1 =40% which is GMP.
Is there a formula that can be used to calculate the GMP from any level of %markup. I have created a test spreadsheet and cannot see nay logical correlation between the markup % and the GMP %. Confused, yes so am I. Are you there VoG?

  VoG II 13:39 14 Feb 08

GMP =C1*B1/A1

  bstb3 13:45 14 Feb 08

but havent tested it completely yet

GMP = 1-(1/(%markup+1)

so in this case, backworking
%markup = 66.67%, or 0.6667.
markup%+1 is 1.6667.
1 / 1.6667 is 0.6
1-0.6=0.4 which is your GMP

so in excel as per your post

d1 would have the formula: =1-(1/c1+1) or
equally it could have =1-(1/(A1/B1))

hope this helps

  bstb3 13:46 14 Feb 08

:)

  johnem 14:12 14 Feb 08

Thank you VoG™ and bstb3.
Tried those, they all work brilliantly.
next observation, is there any way yo calculate the %GMP just from the %Markup. ie D1 from C1 without the input on A1 or B1. My thoughts are that I may still have to have the values in A1 and B1. Any thoughts please?

  VoG II 14:30 14 Feb 08

Try

=1-(1/(C1+1))

  johnem 14:37 14 Feb 08

Thank you VoG™.
Spot on. I must have mistyped the bstb3 formula.

  VoG II 15:10 14 Feb 08

Actually there are unnecessary parentheses in the above

=1-1/(C1+1)

is better.

  bstb3 15:16 14 Feb 08

I put 1-(1/c1+1), should have been 1-1/(c1+1) as per my text explanation, but hey, got there in the end :)

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…