Recent Attendance Percentage Help

[Irukandji81]

Hey all! I am a board member for a new non-profit group and we are looking for ways to track attendance at our meetings. That part is easy. My question is, we also need to be able to see who meets a specific set of criteria.

In short, I need a way to be able to see an entire attendance history for the year, but also have an easy calculation (or conditional format) that looks at the last 10 meetings and sees who has been at, at least, 7 of those 10 meetings. Before I spend time creating a massive spreadsheet for this, I wanted to see if some experts had an easy way to do this. I figured there would be something that could calculate based of the TODAY function but I just cant wrap my head around making that work.

Thoughts?

Thanks for any insight!

-Nathan

[MarvinP]

Hey Nathan,

We need to see a sample worksheet to see the arrangement of your data to give a good answer. Follow the yellow banner at the top of the screen to see how to attach a sample file.

[Richard Buttrey]

Hi,

If you'd like to upload your workbook and manually add some examples of the results you expect then no doubt we can offer a solution. It's always easier if we can see the request in context.

[Irukandji81]

I created a really quick mockup to help you visualize.

-As you can see "Eva" is a Yes in at least 7 of the last 10 meetings (even though she missed early) so she would be a YES in the 7/10 tracking.
-"Tina" is the opposite. She was there a lot early but missed more than 3 of the last 10 meetings so she is a no.
-Finally "Nathan" has perfect attendance so is obviously a yes.

I am looking to track out of the last 10 weeks which is at, at least, 70% attendance.

I hope this helps.

[Irukandji81]

To clarify further - I am essentially looking for a way to automate column B

[Richard Buttrey]

How are you defining the last 10 meetings. The last 10 from row 2, i.e. J2:S2 or the last 10 from and including the current date. i.e. G2:P2

[Irukandji81]

It will be a moving 10 meetings. My example isnt perfectly accurate as we have not had the last 3 meetings in April. Essentially, I need it to look at whatever today is and look back the last 10 meetings. As of today the last 10 would be G:P. As of a week from today though, it will be H:Q, etc. Does that help clarify?

Once I have this all figured out I will build the entire calendar for the year and then we will copy the tab for subsequent years, if that makes sense.

I want our membership team to be able to just open the spreadsheet and take quick attendance at the start of each meeting and the sheet calculates the rest.

[Richard Buttrey]

Hi,

In B5
=IF(COUNTIFS(OFFSET(B5,0,MATCH(TODAY(),$C$2:$S$2),1,-10),"Y")>=7,"Yes","No")

I don't agree that Tina is a no since she has 8 attendances from G10:P10

[Irukandji81]

You are correct Richard I was mistakenly counting the 3 weeks that wouldn't not exist yet in my calculation.

This seems to be working in my test document but it doesnt seem to be working when I put it into their Google Sheets document. Do you know if this is functional in GS by chance? It is displaying "No" which makes me think it is a functional formula, however when I update the formula for the whole year (Goes to column BB and starts on Row 4) it stops working.

Here is the updated formula for that spreadsheet in case I screwed it up in the conversion
=IF(COUNTIFS(OFFSET(B4,0,MATCH(TODAY(),$C$4:$BB$4),1,-10),"Y")>=7,"Yes","No")

I am happy to share a link to the test document if that is allowed and/or helpful.

Thank you SOOOO much for helping me get this close!!!

[Irukandji81]

I went ahead and updated the excel file with the more completed version of the document (that I have been working on since we started talking). You can see I am getting an #N/A for the value when it should be a Yes.

[Richard Buttrey]

The only error you made was to not keep the reference in the MATCH() function to row 2 rather than row 4.

i.e. =IF(COUNTIFS(OFFSET(B4,0,MATCH(TODAY(),$C$2:$BB$2),1,-10),"Y")>=7,"Yes","No")

[Irukandji81]

That fixed it in the Excel document. Seems to be working there. For some reason in the Google Sheet though it always outputs "No". Bummer, I will have to play with this to see if I can figure it out based on this feedback. Otherwise I need to find a way to host this Excel document so it can be editable in Discord for tracking purposes.

Thank you so much for your assistance, I am much closer than I was!!

[Irukandji81]

If anyone happens to have experience with Sheets as well, I am ringing my neck trying to figure out why this isn't working in Sheets. The formula steps through properly, every function is recognized. It is outputting "No" on every row, so it IS calculating...just not properly.

[nick.williams]

I had a look. It seems that Sheets does not permit negative values in the OFFSET function for the height and width of the array. You could try this instead:

=IF(COUNTIFS(OFFSET(B4,0,MATCH(TODAY(),$C$2:$BB$2)-9,1,10),"Y")>=7,"Yes","No")

[Irukandji81]

You are a genius!! Thank you so much! This seems to work. Thank you both for the support!!!!