google-sheets-formula

How to calculate average days of spend return with single arrayformula

In my Google Sheet (see my example sheet here), I have 2 tabs, a Main tab which lists names in A3:A, and a Data Tab in which the names appear somewhere in B1:1, and three columns of data under each name, named SPEND, INCOME, and MISC. A3:A contains Dates in ascending order.

The SPEND columns contain random amounts spent on certain days for each name. The INCOME columns contain random income amounts received on certain days for each name. The MISC columns are for other unrelated data.

Data Tab

On Main Tab, I am trying to figure out how to write a single arrayformula for cell B2 that will calculate the average number of days it takes for each name listed in A2:A to reach their amount spent with their received income.

Main Tab

The only applicable spends for each name are the ones that appear after the first row in which their income is greater than 0, and if the last spend amount has not yet been reached by the income, it is not included in the average either.

The closest I was able to get to this solution was this formula in E3 dragged down to get the average number of days for a specific name (see the Testing Tab in my example): =IF(B3<>"",MATCH(TRUE,INDEX(SUMIF(ROW(C3:C28),"<="&ROW(C3:C28),C3:C28)>=B3,0),0),"") but it isn't starting at the correct row and I am unsure of how to put this into an arrayformula.

Testing Tab

What is the best way to accomplish what I am looking for?

Solution

  • I revised my solution according to suit your needs. As you can see in the image below, I Have only two sheets namely, Main Tab in which we make the calculations and Data Tab which houses the data for the persons listed in the other tab.

    You only need to insert the following formula in cell B2 of the Main Tab and will get the results for the names listed in column A

    =IFERROR(BYROW(A2:A, LAMBDA(PERSON,AVERAGE(LET(HEADERS, 'Data Tab'!$1:$1, myDATA,BYROW(LET(columnSPEND, SUBSTITUTE(ADDRESS(1,MATCH(PERSON,HEADERS,FALSE),4),1,""),INDIRECT("'Data Tab'!" & columnSPEND & 3 & ":" & columnSPEND)),LAMBDA(Ø,LET(columnINCOME, SUBSTITUTE(ADDRESS(1,MATCH(PERSON,HEADERS,FALSE)+1,4),1,""),futureINCOME, INDIRECT("'Data Tab'!" & columnINCOME & ROW(Ø) & ":" & columnINCOME), IF(AND(Ø>0,OFFSET(Ø,0,1)>0,SUM(futureINCOME)>Ø),COUNT(SCAN(Ø,futureINCOME, LAMBDA(acc,val,IF(acc-val>0,acc-val,""))))+1,"")))), FILTER(myDATA,ISNUMBER(myDATA)))))),"")

    .

    enter image description here