% change across columns

Occasional Visitor

How do I calculate % change across 5 columns? I want to see a positive or negative percentage change across 5 data points, for example from May through September

1 Reply

@Johnnie_Johnson It sounds like your data is in consecutive month-specific columns, probably chronological left-to-right.  Do you want a four-month change in percentage for all your monthly data?

 

I will assume that January data is in column B, February data is in column C ... and December data is in column M.  Calculating the four-month percentage changes for the months of May through December is easy, using this formula for May in row 3:

=(F3-B3)/B3

Copy and paste (or drag) that formula to the right, through the month of December.  Format those cells as Percentage, with however many decimal places you want.

 

Formulas for calculating January through April need to be different.  If your rows each contain data for consecutive years (in chronological order going down), the formula to calculate January's four-month percentage change needs to examine data in the prior row (prior year), as:

=(B3-J2)/J2

Copy and paste (or drag) that formula to the right, through April, and format those cells as well.

 

However, that formula causes an unsightly result if applied to the first row of your data.  You could instead use a formula like the following in your January-thru-April cells, to place a hyphen (or an empty string or whatever you choose) where it cannot be calculated, shown again for January of row 3:

=IFERROR( (B3-J2)/J2, "-" )

Copy and paste (or drag) that formula to the right, through April, and format those cells as desired.

 

And where monthly data is not yet available, the formulas for those cells will result in -100%.  If that's undesirable, you could instead use a formula like the following in your May-thru-December cells, shown here for May of row 4:

=IF( F4="", "-", (F4-B4)/B4 )

Again, drag or copy and paste...

 

It should be possible to come up with a single formula that works for the entire data range, but I have made enough assumptions already.  So, is that how your data is structured?  In those columns?