Finding Percentage Difference Gain or Loss

[tylerpick]

I have been trying to get something like this to work for awhile now. I have tried several different formulas with the same result and can't seem to wrap my head around the problem. It works great when I have someone who has lost weight (Julie Ann), but when someone has gained weight (Jim Bob), the formula doesn't work.

Currently the formula I am using is: =(B4-C4)/(C4) I realize that if I change the formula to =(B4-C4)/(B4), it will work for Jim Bob, but then I will encounter the same problem (11.11% loss) with Julie Ann.

Whats wrong with my formula? Is it possible to calculate the gain or loss this way?
If it helps, here is a very basic version of what I am doing: _Weights_Tester.xls

Any guidance on this will be extremely helpful! Thanks in advance for your time..

What is happening:

Name (A3)	Current (B3)	Previous (C3)	% Difference (D3)
Jim Bob (A4)	100 lbs (B4)	90 lbs (C4)	11.11% (D4)
Julie Ann (A5)	90 lbs (B5)	100 lbs (C5)	10.00% (D5)

What should happen:

Name (A3)	Current (B3)	Previous (C3)	% Difference (D3)
Jim Bob (A4)	100 lbs (B4)	90 lbs (C4)	10.00% (D4)
Julie Ann (A5)	90 lbs (B5)	100 lbs (C5)	-10.00% (D5)

[Ace_XL]

While calculating increase/decrease percentages, the base value (denominator) should always be value before the increase/decrease i.e. the 'previous' value I your case

As such, the results in Table 1 are correct, though the 2nd row should read -10%

[AlKey]

And your attached spreadsheet does provide the correct answer -10%

[newdoverman]

The % difference should be based on the STARTING point and not the END point to show either an increase or decrease.

The formula =(b4-c4)/c4 is correct even if it gives you a negative result.

In the first case, the current weight of 100 pounds is a 10 pound increase over the starting weight. That is an increase of 11.11% over the starting weight. Which is correct. =(100-90)/90 =11.11%

The second case, the current weight of 90 pounds is 10 pounds less than the 100 pounds starting weight. This is a decrease of 10% of the starting weight or -10%.... (90-100)/100......= -10/100.....= -10%

[tylerpick]

Thanks guys! I can't believe it was right all along.. and I had been thinking about it backwards..

What matters though is that my issues are resolved Thanks again!

[newdoverman]

Thanks for the feedback. Glad you got straightened out.