What function do I need for getting year-to-date % increase over prior year?

Question

I have an excel workbook for multiple restaurants that has sheets for every year labeled with their sales per month. I am attaching an example of one row of the sheet for the year 2020. I would like to have a cell at the end of each row that shows the % increase or decrease over the prior year up to the last month entered. It would be pulling the information for the results from the prior sheet. I don't know which function to use or how to get the function to only add the corresponding months to date from last year. For example, on the sheet for 2020, sales are listed through July but the sheet for 2019 has all 12 months listed. I would like to include a cell that gives me the year-to-date % through whatever current month it is. Sheet for Year 2020

Answer 1

If you want to use only the columns filled in the current year in the percentage calculation, you can use following formula:

=SUM(B3:M3)/SUMPRODUCT(B2:M2*(B3:M3>0))-1

Answer 2

First count how many cells are blank in your current year using the =COUNTBLANK()

Then sum up the year you are comparing to starting at january then going 12 - result of blankcells formula to the right.

=SUM(OFFSET(JANUARY_CELL_REF, 0, 0, 1, 12-COUNTBLANK(this_year)))

Then you just divide it by the results this year so far:

=result_So_far_this_year / SUM(OFFSET(JANUARY_CELL_REF, 0, 0, 1, 12-COUNTBLANK(this_year)))

Finally format is as a percent.