Need correct formula to track stock gains and losses on a daily basis

[AdamW328]

Hi, can someone help me with this formula for the percent change for the daily open vs the daily close for a stock

For example, using the formula =(B2-D4)/B2 where B2 equals 16 and D4 equals 15, if the open was $16 (B2) and close was $15 (D4), you would get -6.25%, which is fine.

Then if I change B2 to 15 and D4 to 16 and use the same formula, I get -6.7% when it should be a positive 6.7% (because the stock is going from $15 to $16). How can I make it so it would show as positive?

In summary:

If B2 is greater than D4 than the formula should spit out a negative value (IE 16-15/16 would equal -6.25%)
If B2 is less than D4 than the formula should spit out a positive value (IE 15-16/15 would equal 6.67%)

Your help is appreciated. Thanks

[janmorris]

i dont know what you are doing, but when i do the first calculation it is 6.25, and second calculation is -6.67

they are not both negatives as you are describing.

i can of course reverse this behaviour by putting a negative in front of the formula.

[AliGW]

Administrative Note:

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...e-formula.html