Formula to calculate % using negative values
 

(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

Formula to calculate % using negative values
 

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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"cajun-bob" wrote in message
...
(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

Formula to calculate % using negative values
 

<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)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel









"cajun-bob" wrote in message
...
(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

Formula to calculate % using negative values
 

"Niek Otten" wrote:
<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.

First, you did not snip the entire context of the Bob's example. He wrote:

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

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

"Niek Otten" wrote in message
...
<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)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel









"cajun-bob" wrote in message
...
(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