Formula to calculate % using negative values

[cajun-bob]

(this post may appear twice as I am unable to locate my original post)

Greetings,

I'm hoping someone may be able to help me.

I have developed a workbook to track my portfolio on a weekly basis. One
component of my portfolio is my "Line of Credit" which always contain
negative values.

For example, last week I had a -$5,000 balance and this week I have a
-$6,000 balance. The week over week change is -$1,000 or -20%.

Presently, my workbook correctly reflects the -$1,000 weekly change but
shows the percentage change as a positive (rather than negative) 20% weekly
change. The formula for weekly % change that I am using is as follows:
=+(B421-B420)/B420 (where B421 is this week's negative balance of -$6,000
and B420 is last week's negative balance of -$5,000.)

Could you please tell me how to change the formula (or anything else) so
that my workbook will reflect the -20% weekly change?

By the way, the formula I am using to determine my $ weekly change is simply:
=+B421-B420

Thank you.

Bob

 

[Bernard Liengme]

Use =(B421-B420)/ABS(B420)

BTW, you do not need the + sign in an Excel formula. I Lotus we use to write
=A1-B1 while in Excel we use =A1-B1
best wishes

 

[Niek Otten]

<The week over week change is -$1,000 or -20%.>

No. The change is 20%, not -20%.

But if you still want it to show (incorrectly) as -20%:

=(B421-B420)/IF(B420<0,-B420,B420)

 

[Joe User]

<The week over week change is -$1,000 or -20%.>

No. The change is 20%, not -20%.
But if you still want it to show (incorrectly) as -20%:

I think that's a matter of opinion. There is no "correct" or "incorrect"
answer. It depends on how you express it.


If Bob had used the word "decreased" or "increased" by x%, I would agree:
it decreased by 20%.

But Bob said "changed". In normal English, I believe that "decreased by x"
is the same as "changed by negative x".

Consider an example where the balance goes from $5000 to $4000, decreasing
by $1000 or changing by -$1000. I think there is no question that it
"decreased by 20%" or "changed by -20%".

Similarly for the -$1000 change from -$5000 to -$6000.

On the other hand, if we were talking about "losses", not "balances", if
losses change from -$5000 to -$6000, some people say that losses
"increased(!) by 20%". I presume they might also say "changed by 20%".
Likewise, if losses change from -$5000 to -$4000, some people say that
losses "decreased(!) by 20%". Perhaps they might also say "changed by -20%"

Personally, I find that terminology ambiguous at best, if not
counter-intuitive, just like the phase "midnight tomorrow".

(I just made a tentative reservation, and I was told that it would be held
until "midnight tomorrow". I had to ask them if they meant 1 min after
11:59p tonight or tomorrow night. They meant the latter; but technically,
"midnight tomorrow" is the former. And that makes "midnight tonight" an
oxymoron. I always forget how US law defines "midnight"; as I recall, it is
part of the banking code. Off-hand, I think it is defined in such a way
that "midnight tonight" means 1 min after 11:59p today. But I cannot say
that with impunity. Anyway, I digress....)


----- original message -----