Forum Discussion
Solved
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...
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.
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!
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,"")