2

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.

1
  • In the description, you said that on 6/15, the row for 6/15 should be selected, and I went with that (the condition being <= today()). This contradicts your own formula where you write R < ... today().
    – user79865
    Jun 12, 2016 at 23:46

1 Answer 1

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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