Automate Calculating Data

New Contributor



I'm building a spreadsheet that will track monthly metrics and want to automatically calculate changes in two areas:

  1. Percentage change since the initial (baseline) metric
  2. Percentage change since the previous month's metric


I'm attaching a screenshot of how I've set up the spreadsheet. For the sample data, "5" is the baseline metric, "7" is the Month 1 metric, and "8" is the Month 2 metric. Assuming Month 2 is the current month:


For Area #1 above, I want it to show the percentage change since the baseline metric ("5") for the current month's metric ( Month 2, "8") in Column N. However, I want it to automatically update the percentage in Column N when Month 3 (Column J) data is eventually entered (same for Months, 4, 5, and 6).


For Area #2 above, I want it to show the percentage change from the previous month (Month 1, "7") to the current month (Month 2, "8") in Column O. I then want it to automatically update Column O with the change from Month 2 to Month 3 when Month 3 is eventually entered, and so on from Month 3 to 4, Month 4 to 5, etc.

excel examle.PNG

4 Replies
best response confirmed by wispsofsmoke (New Contributor)


In N2:




In O2:




Format both cells as a percentage.

The formulas can be filled down.

Perfect, thanks - totally worked!

One follow-up question: let's say I don't have data for a given month, e.g., Month 3. Is there a way to modify the formula or enter something in the cell so it will revert to the most recent month that does have data, e.g., Month 4 to Month 2?

It's not a big deal if it can't be done or it's too complicated, but otherwise would be curious.



In N2:


In O2:


@Hans Vogelaar