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