I would like to calculate the percent change of a quantity for each time it appears associated with a particular string.
The example uses salaries and names. Each time a person salary is updated, it is added to the sheet. I want to automatically calculate the percent change from their previous salary listed.
I am seeking a solution that does not require a drag down with each entry (e.g., ArrayFormula, BYROW).
sample spreadsheet: https://docs.google.com/spreadsheets/d/1v8Wg_DCLDSyiEA-I50hITeYAMrlFdGldT2eEB_YDdN4/edit?usp=sharing
currently, there are two drag down solutions that work: K2
=IFERROR((INDEX(QUERY(INDIRECT("A2:J"&ROW(A2)),"select J,H,A where J contains ‘"&J2&"’ order by A desc"),1,2)-
INDEX(QUERY(INDIRECT("A2:J"&ROW(A2)),"select J,H,A where J contains ‘"&J2&"’ order by A desc"),2,2))/
INDEX(QUERY(INDIRECT("A2:J"&ROW(A2)),"select J,H,A where J contains ‘"&J2&"’ order by A desc"),2,2))
and N2
=IFERROR((INDEX(SORT(FILTER({INDIRECT("J2:J"&ROW(A2:A)),INDIRECT("H2:H"&ROW(A2:A)),INDIRECT("A2:A"&ROW(A2:A))},INDIRECT("J2:J"&ROW(A2:A))=J2),3,FALSE),1,2)-
INDEX(SORT(FILTER({INDIRECT("J2:J"&ROW(A2:A)),INDIRECT("H2:H"&ROW(A2:A)),INDIRECT("A2:A"&ROW(A2:A))},INDIRECT("J2:J"&ROW(A2:A))=J2),3,FALSE),2,2))/
INDEX(SORT(FILTER({INDIRECT("J2:J"&ROW(A2:A)),INDIRECT("H2:H"&ROW(A2:A)),INDIRECT("A2:A"&ROW(A2:A))},INDIRECT("J2:J"&ROW(A2:A))=J2),3,FALSE),2,2))
I tried another route (M2) but could not get the range for each row to restrict to the current row.
=ArrayFormula(IF(J2:J="",,BYROW(J2:J,LAMBDA(name,INDEX(QUERY(FILTER({$J$2:$J,$H$2:$H,$A$2:$A},$J$2:$J=name),"order by Col3",0),1,2)))))
If there is a simpler/alternative option (e.g., lookups, index/match, etc), I’m open to taking a completely different approach.
>Solution :
You can significantly simplify the formula using BYROW() and LET(). Try the following formula-
=BYROW(J2:J20,LAMBDA(x,
LET(t,SORT(FILTER(H2:H,J2:J=x,INDEX(ROW(H2:H))<=ROW(x)),1,0),
IFERROR((INDEX(t,1)-INDEX(t,2))/INDEX(t,2),""))))
