• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Percentage calculation problems [SOLVED]

robatkinson

New Member
Hi, first post so forgive me if this has been covered elsewhere!


I have a spreadsheet that looks at two cells and calculates the % variance. The difficulty I am having though is that some of the cells are negative figures and this seems to be throwing out the results.

Example:


Value 2008 = -100(Cell A1)

Value 2009 = -50 (Cell A2)


Using the formula =(A2-A1)/A1


Gives me an answer of -50%

In reality the performance is better by 50% so to get a positive answer I initially put a - between the = and the opening bracket.


If the numbers are all positive as per the next example then the original formula works.


Value 2008 = 100(Cell A1)

Value 2009 = 200(Cell A2)


Using the formula =(A2-A1)/A1


Gives me an answer of 100% which is what I would expect. I just want to use 1 formuala that works without having to go into each sheet and change each line every week.


So my question is - Is there a formula that will recognise negatives within the calculation but will handle them as I would expect? I am sure there must be an easy work round for this. All help appreciated.


Rob
 

robatkinson

New Member
Tried this but it doesnt seem to work. The spreadsheet has some weeks when both weeks are negative, some when both are positive and some where either the first or the second week is negative. A colleague has suggested that I need a nested IF statement. He's working on it now. I'll let you know if it works. Thanks for your prompt reply.
 

Stephane Nolf

New Member
You should use the following formula : =(A2-A1)/ABS(A1).


So if the difference between A2 and A1 is above (below) zero, the % will be greater (lower) than 0.
 
Top