How to calculate the percentage difference between values in a row by skipping blanks

Copper Contributor

Hello all, I am using Excel on a PC, version 2302 build 16.0.16130.20332. I am looking for a formula to help me calculate the percentage difference of quantities between months. The problem is, there are often 'blank' months and I need the percentage difference between the last known value, skipping the blank months. Here is an example:

SheetExample2.png

On the left is my pivot table, on the right are the months where I'd ideally like the percentages to show up. On row 29, from July to September, there is a 40% decrease in the values. But I'm not sure how to create a formula that works for the entire Sept. column that ignores blank months, like how August is blank in row 29 and row 40. And if there is no value before Sept, like in row 41, there will not be a calculation until the next value (which is October). Ideally, I'd like to create a formula in the months on the right side of the pivot table that only pulls a percentage in the months that have an increase or decrease from the last known value. If there is no value/no calculation triggered, it'll just be "". 

I'm thinking each column might need a slightly different formula to extend the range between months, which is fine, but I haven't had any luck getting the results I'm looking for. I'm hoping I was able to explain this well enough! Thank you for your consideration.

1 Reply

@sarahprkr23 

In N29:

=IF(C29="", "", IFERROR(C29/LOOKUP(9.99999999999999E+307,$B29:B29)-1, 0))

Format as a percentage, then fill to the right to V29, and fill down as far as you want.