Hi all,
Simplified:
I have a this years total in A1, last years total in B1, difference +/- in C1
$1000.00___$800.00___+$200.00___+%
$800.00___$1000.00___<$200.00>___<%>
I am trying to get a % of Increase/Decrease (Between A1 and B1) in D1.
Not as a dollar amount but as a %.
I have tried Percentile and PercentRank and some other stuff. No joy.
Any direction?
Thx
Dave
"The game is afoot Watson"
with column D format set to percentage try
=(A1/B1)-100%
Hi Mudraker,Originally Posted by mudraker
Its working good. Not being a math person, i had a hard time getting my head
around the difference in the +/- results, with the same input values, but they
are correct.
IE:
YTD__LY___%
25___50___-50.0%
50___25___100.0%
Why do you subtract the -100% in your formula?
Thanks a lot.
I am no maths/Excel expert either so i can not fully explain why Without the -100% Excel gives a % increase of 125% when it is 25%. using your 1st example of $1000.00___$800.00___+$200.00___+% but I will try
$1000 is 1.25 times greater than $800 when expressed as a % this comes out as 125%.
Subtracting 100% gives the actual increase. of 25%
Hi,
Using Percentage is only a Format display ... it is not a built-in function
HTH
Cheers
Carim
Hi,
Thanks Carim and mudraker, for your responces. You have made an old man happy.
I did a search and found this thread which is a similar problem to mine, but I haven't got the information above to work yet. For me, it's a monthly comparison with each month being on a different row. It starts in October with the total in E4 and then the November total in E5.
In column F, I would like it to show the percentage difference with positive figures (increased turnover) in green and negative figures in red. The spreadsheet will then show at a glance how I'm doing compared to the previous month. Hopefully in the future, I can use your suggestion to produce an annual equivalent.
In F5 (November row), I entered =(E4/E5)-100% which game me an 87% increase. In reality, October (row 4) is 530.31 which goes down to 280.50 for November (row 5), this should be a difference of about 47% less.
Can anyone say where I'm going wrong? Thank you.
Last edited by ianonline; 01-24-2007 at 05:29 PM.
Yes, you have the 2 cells reversed. It should be:
=(E5/E4)-100%
Or, more simply:
=E5/E4-1
Thanks Jason, obviously Maths is not my strong point!! That formula works well for the first row. I copy + pasted it down and I find -22 (loss) last month up to a 262 (profit) so far this month gives -1291%, shouldn't that be a big increase?! I'm thinking maybe I should get a dedicated accounting package, but would it really help?! This all looks correct on the calculator, I just can't do accounting to save my life, that's what accountants are for!
Here's an image so far: click here
Last edited by ianonline; 01-24-2007 at 06:28 PM.
The problem is that you can't really calculate a % increase from a negative number to positive. It's just not logical (similar to try to divide by 0). Can you think of any other way you might want to look at the difference between the prior and current months?
I can't really think of any alternative. The good news is I should consistently be in positive figures from now on! I often read about some stock price being up X% or down X%, so how do they work it out?
Well, that formula above will work just fine for stock prices, since stock prices are never negative.
Yeah, I just found that out. it's obvious when I think about it! Hopefully this helps others! Thanks for your help. This formula gives a good incentive to stay in the black as it's so much clearer then!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks