Percentage calculation problems [SOLVED]

[robatkinson]

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

 

[keymaster]

you need to use the ABS() formula. just wrap the original formula in this. =ABS((A2-A1)/A1)

 

[robatkinson]

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.

 

[ericlind]

It should work, but you have to apply the Absolute Value function to both source cells.

 

[Stephane Nolf]

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.

 

[robatkinson]

Many thanks Stephane.


This solved my problem.


Its strange that I have not come across this sort of error before.