3

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

3
  • This blog seemed to give a hint, but the exact table and formula resulted in a formula parse error
    – beeshyams
    May 21 at 17:51
  • Would putting a conditional in the 'Days' column solve your problem, eg. =If(b2='pear',0,c1+1)? If it makes sense will add an answer. May 21 at 18:04
  • @AndroidNovice I tried something similar but it didn't help; I will try again tomorrow
    – beeshyams
    May 21 at 18:06

2 Answers 2

3

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.

1
  • David, works perfectly, thanks and added an answer to incorporate in appscripts! I would be happy if you could answer this question as well. That is my actual need but I was too tired when I wrote this question and populated the dates column wrongly!
    – beeshyams
    May 22 at 9:29
2

For completeness ,building up On David's answer, since I wanted to use it in App script as asked in the question, here's the declaration that works fine

const cell = sh.getRange(`C:C${sh.getLastRow()}`); cell.setFormula(`=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"")`);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.