Google Sheets: QUERY most recent date prior to today

0
votes

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.

Any help would be much appreciated!

UPDATE

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.

2
google-sheets

2 Answers

0
votes

Try this one:

=QUERY(R:AF,
"select R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF 
where R = date '" & text(INDEX(R:R,MATCH(TODAY(),R:R,1)),"yyyy-MM-dd") & "'",0)

The key is using MATCH(TODAY(),R:R,1) the third parameter (1) will give not exact match, but just the position of date you want:

1, the default, causes MATCH to assume that the range is sorted in ascending order and return the largest value less than or equal to search_key.

0
votes

I am sure there must be a better way to do this, but this works. Adjust the ranges (R:AF) to fit your spreadsheet.

=query(transpose(query(R:AF,"select R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF where   datediff(now(),R)>=0 and datediff(now(),R)<=14",0)),"select count(Col1) where Col1 ='y' label count(Col1)''")/query(transpose(query(R:AF,"select R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF where datediff(now(),R)>=0 and datediff(now(),R)<=14",0)),"select count(Col1) where Col1 ='n' label count(Col1)''")