Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate the difference between negative and positive values | Excel Worksheet Functions | |||
Formula for Negative Values - ? | Excel Worksheet Functions | |||
Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) | |||
Formula to calculate only the negative numbers | Excel Worksheet Functions |