Unique Count, Average Percentage, and Weekly Breakdown - Member Check-Ins

Originally Posted by Glenn Kennedy

Members per age group:

=SUM(INDEX(('List of Members'!$D$2:$D$50>=A2)*('List of Members'!$D$2:$D$50<=B2)*('List of Members'!$D$2:$D$50<>"")/COUNTIFS('List of Members'!$D$2:$D$50,'List of Members'!$D$2:$D$50&""),0))

Average checkins:
=IFERROR('Total Members per Age Group'!C2/'Total Vists per Age Group'!C2,"")

The last bit has me a bit confused. Can you check the layout of how you want it to look? add a few manually calculated results and repost the sheet here.

Hi Glenn,

Thank you very much for your help on the first two items.

Sorry for the confusion on the last part. Essentially, it's the same calculations as on tab 4, which is the average, but just with added criteria of each week during the month.

I've reattached the sheet with an example of how I'd like it to look with the manual calculations.

Originally Posted by belinda200

Hi,

as for your last request:
here is the total members % per week,
see "Average Checkins per AG+Week" tab where I created a new table showing the % of visitors per age/week.

In C11:

Please Login or Register  to view this content.

In order to get the percentage divide the result by the number of visitors like that:

Please Login or Register  to view this content.

Hi belinda,

Thank you for taking the time to help.

It doesn't seem accurate though, as it's showing 22% for week 1 for ages 13-19. There's only 2 check-ins during that date range, and it's the same person. The calculation would be unique number of members divided by the number of visits, so 1 / 2, which should be 50%. The 22% would be the overall total.

OK, i didnt notice there were names on the list of members. are you using office 2016?

Originally Posted by belinda200

OK, i didnt notice there were names on the list of members. are you using office 2016?

Yes I am, thank you!

Originally Posted by MorganKlaif

Hi belinda,

Thank you for taking the time to help.

It doesn't seem accurate though, as it's showing 22% for week 1 for ages 13-19. There's only 2 check-ins during that date range, and it's the same person. The calculation would be unique number of members divided by the number of visits, so 1 / 2, which should be 50%. The 22% would be the overall total.

Total visits for ages 13-19 is 9 according to your file, please correct me if i'm wrong....

Originally Posted by belinda200

Total visits for ages 13-19 is 9 according to your file, please correct me if i'm wrong....

Hi belinda,

That's correct. It's 9 in total, but it would only be 2 for the current issue, as there's only 2 visits within the week 1 date range. It's the same person/account. So, it would be the 1/2 equation, making it 50%.

I hope that clears it up!

So - I ended up with a crazy formula to eliminate the duplicates

C11 and across:

Please Login or Register  to view this content.

Check your manual results as they seem wrong. week 3 had 2 visitors out of 2 for example so the result should be 100%

Originally Posted by belinda200

So - I ended up with a crazy formula to eliminate the duplicates

C11 and across:

Please Login or Register  to view this content.

Check your manual results as they seem wrong. week 3 had 2 visitors out of 2 for example so the result should be 100%

Hi belinda,

I really appreciate all of your help, but the calculations still seem a bit wrong.

I've attached a newly updated spreadsheet with everything that Glenn added, double checked my manual entries, and color coded a few things to make it a bit easier to see/understand.

Week 1 in what you have seems to be the only correct one. In the newly attached sheet, the calculations should be:

1. For week 2, there is only 1 check-in. Yours shows 50%, but it should be 100%, as it would be "1 Member / 1 Visit".
2. For week 3, yours has 100% for both 13-19 and 20-29. For 13-19, there's 2 unique members (First 1 and First 2) and 4 visits between the 2 of them, so it would be "2 Members / 4 Visits" for 50%. For 20-29, there's 1 member and 2 visits, so it would be "1 Member / 2 Visits" for 50%.
3. For week 4, there is only 1 check-in. Yours shows 50%, but it should be 100%, as it would be "1 Member / 1 Visit".

Sorry for all the back and forth and confusion, but hopefully the above explanation helps!

Hi belinda,

Week 3 is 01/17/2022 to 01/23/2022.

On 01/18/2022, First 1 checked in. 1 visit.
On 01/20/20222, First 1 and First 2 checked in. 2 visits.
On 01/21/2022, First 2 checked in. 1 visit.

OK you should have mentioned that you count the visits per day and not per week, but you do count the number of visitors per week...

This has resulted in a monstrous formula so I wont copy it here, just see attached at the "Average Checkins per AG+Week" tab....

Hi belinda,

I'm so sorry for the confusion and the back and forth. It looks like everything is working perfectly!

Thank you both for all of your help!

I'm good to go now!

Finally ! you're welcome...

With the LET function - this can be much shorter...

Please Login or Register  to view this content.