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.
=(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.