0

I've searched a sea of results but haven't found a solid answer to my specific question. Hopefully someone can help me out. I'm using cells C2,D2, and E2. Here's what I'm after. In cell E2, if C2 is greater than D2, E2 must display C2-D2/C2x-100, or if C2 is less than D2, E2 must display C2-D2/C2x100. The formula for those interested is stocks, if the current price of a stock is less than the opening price of the stock, display the percentage decrease (negative number) and vice versa.

1
0

You are probably looking for this:

=iferror( (C2 - D2) / D2 )

With the current price 101 and opening price 100 this will give 1.00%.

With the current price 99 and opening price 100 this will give -1.00%.

That is however not what you asked for. Your description calls for the result to always be a negative percentage, because you specify that the denominator should take the sign that is opposite to the sign of the numerator. I think this formula implements what you are asking for:

=iferror( (C2 - D2) / (C2 * sign(D2 - C2) * 100) )

With the current price 101 and opening price 100 this will give -0.01%.

With the current price 99 and opening price 100 this will also give -0.01%.

Alternatively, taking your description literally, we get:

=iferror( C2 - (D2 / C2) * sign(D2 - C2) * 100 )

...which gives 20000.99% and -201.01% with the above inputs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.