Formula for matrix (dates in ascending order)

formulasgoogle sheetsgoogle-apps-script

Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts)

Let's say I have a table that is updated daily showing the fruit Tony eats on that day

For some reason, I need to find the number of days between eating Pear followed by Apple. Here it is manually done by subtracting the dates

How would I translate this into a formula? I tried reading up on VLOOKUP, INDEX,SMALL, XLOOKUP but didn't get a hang of the approach

I am aware the on SE, one needs to show what has been done by OP to solve but in this case, I am totally confused, sorry again.

enter image description here

Best Answer

I know you are looking for an Google App Script answer, but I have a formula:

=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"")

This formula goes on the third column. It basically first checks if the B column is an apple, then it will filter all the pears with dates lower than itself. Then that filtered array gets sorted to give the most recent one, then you just minus that with the apple date. A value minus an array means that you are going to do the operation with the first cell in that array, and that's why we have to sort it. Hopefully this helps.

Related Topic