Conditional Formula: As more data is entered the range of cells in calculation changes

[jw8390]

In E5-E16 I have data from one year. In Column H5-H16 I have the same data type for the next year. I want to see the year to date increase/decrease in percentage which for each month I have Calculated in Column "I" as =(H5-E5)/E5 and so on down through E16.

I have data complete and in all cells E5-E16. I only have data in cells H5-H7.

I wish to have a formula that calculates The sum of all of the values in cells H5-H16 and where the first 0 value occurs in that cell range occurs the (in this case H8) the formula sums the total of the values in Cells E5-E16 that correspond (in this case E5-E7) and perform the calculation as I have such that the percent change is provided as the result. As more data is entered in cells H8, H9 and so on the calculation should include those cells and the corresponding cells in column E providing a year to date percent change.

Any ideas or solutions is much appreciated. To me it seems like there is a really long formula that could solve but it also seems like something that I may be overthinking

 

[jono19872006]

Hi
You could add in a formula in an extra column as a “helper” column and work from there.
You could use a match formula to work out the first blank cell eg MATCH(0,H5:H16,0) – this would look for the first row with a zero as you said (although I assume you mean a blank cell? In which case this wouldn’t work)
If there were zero’s you could combine the match formula with an offset to work out (if this is what you want to do I could knock the formula together for you?) – this would rely on the remaining months having a zero (And the months that have already gone never being able to equal zero)

You could do a really long nested If statement but this isn’t the cleanest way…

 

[jono19872006]

Actually it depends on how you are dealing with the data – if you are adding in:
You could use the formulas below which create an array formula so will work down and cross reference where the cell doesn’t equal blank and sum – nb the “” can be replaced by zero if that represents the remaining months.

The only issue would be this doesn’t recognize the last cell ie if month 5 was blank it would carry on looking in month 6 etc and sum up

Sum of Col H - =SUMPRODUCT(H5:H16,--(H5:H16<>""))
Sum of Col E - =SUMPRODUCT(E5:E16,--(H5:H16<>""))