0

I'm looking to include in column H of my spreadsheet as profit as a %. Such as if you paid $5, and sold for $10, you made 200% profit or 2x your money, obviously excel will only recognize 200%, not 2x. But I am getting some crazy numbers, so obviously I'm not doing something right. Column B is what I paid, and Column F is what I sold it for.

@ Gary's Student, This is a good formula, but for my first example, Paid $1, sold for $50, my percentage comes out as 4900%? Everyone after that comes out correct.

Improve this question
6
  • What is the formula you have, and what are the "crazy numbers"? Is there some reason e.g. =F1/B1 doesn't do the trick?
    – eirikdaude
    May 20, 2015 at 10:28
  • @ Gary's Student, This is a good formula, but for my first example, Paid $1, sold for $50, my percentage comes out as 4900%? Everyone after that comes out correct.
    – user449330
    May 20, 2015 at 11:15
  • Buying for $5 and selling for $10 is a 100% profit, not 200%. (You make $5 in the end when subtracting the $5 paid from the $10 received, and $10 is 100% more than $5, hence 100% profit.) The sell price in that case however is 200% of the purchase price, but that's not the same thing. In calculating profit you need to take cost into account.
    – user
    May 20, 2015 at 11:19
  • If you are getting 4900% then you have probably left out the parentheses from the formula. 50-1/1 = 49 which, when formatted as a percentage in Excel, will be 4900%. You want to calculate (50-1)/1, not 50-1/1.
    – Mike Fitzpatrick
    May 20, 2015 at 11:59
  • @ Mike Fitzpatrick, I do have the parentheses around the calculation, and I am still getting the 4900%. The proper profit % should be 490%. Not sure why there is an extra zero.
    – user449330
    May 20, 2015 at 13:45

2 Answers 2

Reset to default
2

Use a better formula:

Percent Profit = (Sell Price-Purchase Price) / Purchase Price

enter image description here

See:

Formula for Percentage Profit

Improve this answer
0 
=($F2/$B2)*100

should do what you want it to

Cases: enter image description here

If you then want to tidy it up a bit, simply format the column to 2DP, set it as a percentage etc. If you want to be really fancy, you can look at doing this within your Formula and use something like:

=ROUND(((F5/B5)*100),2)&"%"
Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .