Automate Calculating Data

[wispsofsmoke]

Hi,

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.

[#2 · Accepted solution]

@wispsofsmoke 

In N2:

=INDEX(G2:M2,COUNT(G2:M2))/G2-1

In O2:

=INDEX(G2:M2,COUNT(G2:M2))/INDEX(G2:M2,COUNT(G2:M2)-1)-1

Format both cells as a percentage.

The formulas can be filled down.

[#3]

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.

Thanks!

[#4]

@wispsofsmoke 

In N2:

=INDEX(G2:M2,LARGE(IF(G2:M2<>"",COLUMN(G2:M2)-COLUMN(G2)+1),1))/G2-1

In O2:

=IFERROR(INDEX(G2:M2,LARGE(IF(G2:M2<>"",COLUMN(G2:M2)-COLUMN(G2)+1),1))/INDEX(G2:M2,LARGE(IF(G2:M2<>"",COLUMN(G2:M2)-COLUMN(G2)+1),2))-1,"")

[#5]

@Hans Vogelaar 

Thanks!!!!!!!!