QUERY most recent date prior to today

Question

I have list of dates in column R that are spaced by two week intervals, so 6/1/16, 6/15/16, 6/29/16, etc.

In columns S to AF I will mark "y" or "n" each day on the row corresponding to the date, during the two weeks until the next date.

I would like to calculate the percentage of "y" entries in S:AF for the Current Period in cell P12.

I have =(countif(S16:AF16,"y")/counta(S16:AF16)) which is accurately calculating the percentage of "y" entries in row 16, but I am trying to figure out a query that will select the row with the Current Period (i.e. The most recent date prior to today's date). So if today is 6/1/16, I want it to give me the percentage of "y" entries in the row with 6/1/16. If today is 6/14/16, I want it to give me the percentage for the row with 6/1/16. But when today is 6/15/16, I want it to start giving me the percentage for the row with 6/15/16, and so on.

I've tried a few different query setups and ended up with something so jumbled that I don't even know where to begin now. I was trying to do something where it would query all the dates prior to today() and then select the MAX of those, but I couldn't get it to work.

---

I finally figured out this query, which finds all the dates prior to today:

=query(R15:AF,"select R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF where R < date '" & text(today(),"yyyy-MM-dd") & "'",0)

I can put a MAX() around this to get only the most recent date. However, I still can't figure out how to apply the =(countif(S16:AF16,"y")/counta(S16:AF16)) equation so that this percentage is returned for whatever the most recent date is.

Answer 1

I would use filter instead of query. The function

=counta(filter(S1:AF, R1:R<=today(), R1:R>today()-14))

counts the number of nonempty entries in the row with the date that falls after today()-14 but not later than today(). The logic is simple: filter the rows, returning the content of their columns S through AF, then apply counta.

To get the count of entries with "y", a second filter is used:

=counta(filter(filter(S1:AF, R1:R<=today(), R1:R>today()-14),  S1:AF1="y"))

The final result, the proportion, is obtained by dividing the latter by the former:

=counta(filter(filter(S1:AF, R1:R<=today(), R1:R>today()-14),  S1:AF1="y")) / counta(filter(S1:AF, R1:R<=today(), R1:R>today()-14))