Friday, 31 January 2014

Excel - Calculate the change between values in percentage

Introduction
Would you like to find the percentage difference between values? Perhaps you want to see the difference between figures from year to year, for example, we have 15% more calls than last year. To do this you can use the formula:
=(B2-A2)/B2


The following are the steps I took to create the above:
  • I created two columns, 2012 and 2013
  • I entered values for each column
  • I entered the formula =(B2-A2)/B2 in C2
  • I dragged C2 down so that the formula filled the cells C3 to C5
  • I formatted Column C as a Percentage %

I've used simple values so you can see the formula is working. In fact this is a good general tip when you are working with Excel formulas.

NOTE: Instead of formatting the cell as a Percentage %, an alternative is to put % on the end of the formula, for example:
=(B2-A2)/B2%
...the above would give a percentage formatted result without having to use Format Cells.


Absolute or Not?
In our above example cell A4 (2012) has a larger value than B4 (2013). This means our result is a minus (“-11%” in our example). It indicates a drop and that might be what you want. Perhaps you need to distinguish between an increase and decrease between the values. In this case the above formula is fine.


However, there are times when you just want to show the percentage as a ‘change’ or the ‘difference’. In this case you can use the ABS() function. The ABS() function removes the minus ("-") sign from the result. Here's an example formula: 
=ABS(B2-A2)/B2


In the above example you can see the result is now 11% and not -11%.



Excel Version?
I used Excel 2013 in this example but this will work for Excel 2010 and earlier versions too.



Post a Comment