Excel calculation required

  johnem 13:20 14 Feb 2008

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 2008

GMP =C1*B1/A1

  bstb3 13:45 14 Feb 2008

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 2008


  johnem 14:12 14 Feb 2008

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 2008



  johnem 14:37 14 Feb 2008

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

  VoG II 15:10 14 Feb 2008

Actually there are unnecessary parentheses in the above


is better.

  bstb3 15:16 14 Feb 2008

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.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

How to update iOS on iPhone or iPad

Les meilleures applications pour enfants 2017