Google Sheets: QUERY most recent date prior to today
<p>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.</p>

<p>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.</p>

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

<p>I have <code>=(countif(S16:AF16,"y")/counta(S16:AF16))</code> 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.</p>

<p>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 <code>today()</code> and then select the MAX of those, but I couldn't get it to work.</p>

<p>Any help would be much appreciated!</p>

<hr>

<p>UPDATE</p>

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

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

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