Sep 06 2022 02:16 PM
Hi,
I'm building a spreadsheet that will track monthly metrics and want to automatically calculate changes in two areas:
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.
Sep 06 2022 02:55 PM
SolutionIn 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.
Sep 06 2022 03:32 PM
Sep 07 2022 04:14 AM
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,"")
Sep 06 2022 02:55 PM
SolutionIn 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.