Trying to make a percent change formula

[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.

Open was $16 (B2) and close was $15 (D4) so this equals -6.25% when using the above formula, which is fine.

Then if I change B2 to 15 and D4 to 16 and use the formula I get -6.7% when it should be a positive 6.7%. How can I make it so it would show as positive?

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

[Ghost12]

One of the solutions is to use this formula: =if(B2>D4,-(B2-D4)/B2,(B2-D4)/B2).
And if you want to highlight negative values go to Conditional Formatting (on Home Tab) > New Rule. Then select "Format only cells that contain". Then select cell value less then 0 and press "Format" button. Then you can change color of a cell or font for negative numbers

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[JohnTopley]

Why not simply "Close" -"Opening" which (to my mind) is the logical relationship.

[AdamW328]

Ghost12, ty for your response. I made an error on the formula and edited my original post. Can you please take a 2nd look.

Or anyone else. Thanks and sorry for the confusion.

[Ghost12]

That should work:
=IF(B2>D4,-(B2-D4)/B2,ABS((B2-D4)/B2))

[curiouscat408]

For example, using the formula =(B2-D4)/B2 where B2 equals 16 and D4 equals 15.
Open was $16 (B2) and close was $15 (D4) so this equals -6.25% when using the above formula, which is fine.

I made an error on the formula and edited my original post.

I don't know what you changed. But the formula that you have now (above) does not fit the facts in the description.

In particular, (B2-D4)/B2 = (16-15)/16 = 1/16 = (positive) 6.25%, not -6.25%.

In any case, the correct formula is simply =(D4-B2)/B2 or equivalently =D4/B2-1, formatted as Percentage.