Forum Discussion
wispsofsmoke
Sep 06, 2022Copper Contributor
Automate Calculating Data
Hi,
I'm building a spreadsheet that will track monthly metrics and want to automatically calculate changes in two areas:
- Percentage change since the initial (baseline) metric
- 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.
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.
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.
- wispsofsmokeCopper ContributorPerfect, 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!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,"")