Formula to find average number of occurrences each day in a given month

[kcmuppet]

Hi all,

I'm stuck trying to work out the average number of patients we get per day (each month) from a list of appointments like below.

I started by doing a pivot table with dates as rows grouped by month, but 'Count of Patients' seemed to actually produce a count of rows, not specific / unique patients.

So I resorted to creating a list of unique dates, then counting a filtered list of unique patients against each date, then creating a pivot table of that produced what I think are the right answers that I'm looking for

But how can I do it with a formula for a given month, instead of extra lists and pivot tables?

Book1
	B	C	D	E	F	G	H	I	J
5	Date	Day	Patient		Unique dates	Unique Patients Per day
6	03 Aug 21	Tue	00008		03 Aug 21	1		Pivot table source is Cols F & G
7	04 Aug 21	Wed	00060		04 Aug 21	1
8	05 Aug 21	Thu	00024		05 Aug 21	1		Row Labels	Average Patients per day
9	07 Aug 21	Sat	00061		07 Aug 21	1		2021	4.0
10	10 Aug 21	Tue	00008		10 Aug 21	1		Aug	1.0
11	11 Aug 21	Wed	00058		11 Aug 21	1		Sep	3.8
12	12 Aug 21	Thu	00062		12 Aug 21	1		Oct	5.6
13	14 Aug 21	Sat	00057		14 Aug 21	1		Nov	5.0
14	17 Aug 21	Tue	00008		17 Aug 21	1		Dec	4.5
15	18 Aug 21	Wed	00008		18 Aug 21	1		2022	3.9
16	19 Aug 21	Thu	00063		19 Aug 21	1		Jan	4.9
17	21 Aug 21	Sat	00033		21 Aug 21	1		Feb	3.7
18	24 Aug 21	Tue	00015		24 Aug 21	1		Mar	3.9
19	25 Aug 21	Wed	00014		25 Aug 21	1		Apr	3.3
20	26 Aug 21	Thu	00057		26 Aug 21	1		May	3.6
21	28 Aug 21	Sat	00002		28 Aug 21	1		Grand Total	3.9
22	01 Sep 21	Wed	00001		01 Sep 21	5
23	01 Sep 21	Wed	00002		02 Sep 21	2
24	01 Sep 21	Wed	00003		04 Sep 21	1
25	01 Sep 21	Wed	00004		07 Sep 21	2
26	01 Sep 21	Wed	00005		08 Sep 21	3
27	01 Sep 21	Wed	00005		09 Sep 21	2
28	01 Sep 21	Wed	00001		11 Sep 21	8
29	01 Sep 21	Wed	00002		14 Sep 21	3
30	01 Sep 21	Wed	00003		15 Sep 21	4
31	01 Sep 21	Wed	00004		16 Sep 21	4
Sheet1

Cell Formulas
Range	Formula
F6:F162	F6	=SORT(UNIQUE(Table1[Date]))
C6:C31	C6	=TEXT([@Date],"ddd")
G6:G31	G6	=COUNTA(UNIQUE(FILTER(Table1[Patient],Table1[Date]=F6)))
Dynamic array formulas.

 

[Eric W]

Try:

Book3
	A	B	C	D	E	F	G
1
2
3
4
5		Date	Day	Patient		Month	Average patients per day
6		8/3/2021	Tue	8		Aug 2021	1
7		8/4/2021	Wed	60		Sep 2021	5
8		8/5/2021	Thu	24
9		8/7/2021	Sat	61
10		8/10/2021	Tue	8
11		8/11/2021	Wed	58
12		8/12/2021	Thu	62
13		8/14/2021	Sat	57
14		8/17/2021	Tue	8
15		8/18/2021	Wed	8
16		8/19/2021	Thu	63
17		8/21/2021	Sat	33
18		8/24/2021	Tue	15
19		8/25/2021	Wed	14
20		8/26/2021	Thu	57
21		8/28/2021	Sat	2
22		9/1/2021	Wed	1
23		9/1/2021	Wed	2
24		9/1/2021	Wed	3
25		9/1/2021	Wed	4
26		9/1/2021	Wed	5
27		9/1/2021	Wed	5
28		9/1/2021	Wed	1
29		9/1/2021	Wed	2
30		9/1/2021	Wed	3
31		9/1/2021	Wed	4
32
Sheet15

Cell Formulas
Range	Formula
F6:F7	F6	=UNIQUE(Table1[Date]-DAY(Table1[Date])+1)
G6:G7	G6	=LET(u,UNIQUE(FILTER(Table1,(MONTH(Table1[Date])=MONTH(F6))*(YEAR(Table1[Date])=YEAR(F6)))),f,FREQUENCY(INDEX(u,0,1),SEQUENCE(31,,F6)),SUM(f)/ROWS(FILTER(f,f>0)))
C6:C31	C6	=TEXT([@Date],"ddd")
Dynamic array formulas.

The F6 formula gives you a list of unique months. It's actually the 1st of each month, but I formatted it to just show the month/year. The G6 formula you'll need to drag down. It finds the average number of unique patients per day. Note that it only includes days that have patients. It does not include 8/8/2021 or 8/9/2021 in the average.

 

[kcmuppet]

Wow!

That's quite a formula. If you have time could you help me understand it?

I think the LET function is creating variables u, and array of the dates and patients for the month and year in question, derived from filtering the main table, and f, a list of the number of times a unique patient appears for each of the days in a given month.

I can follow the first part OK:

LET(u,UNIQUE(FILTER(Table1,(MONTH(Table1[Date])=MONTH(F6))*(YEAR(Table1[Date])=YEAR(F6))))

The second part is hard for me to follow, in particular the FREQUENCY & INDEX

f,FREQUENCY(INDEX(u,0,1),SEQUENCE(31,,F6)),SUM(f)/ROWS(FILTER(f,f>0)))

SEQUENCE(31,,F6)

seems to be generating a list of all the 31 days from the start of the given month (which makes me wonder what happens if its in a month with fewer than 30 days, but it seems to work perfectly).

FREQUENCY(INDEX(u,0,1),SEQUENCE(31,,F6))

then seems to count the number of times each of the next 31 days from the beginning of the month features in the first list of dates and patients for the month - variable u. I'm wondering if it's possible to to do this using COUNTIF, but I guess it needs to be an array, so I would have next wrestled with FILTER. I read somewhere that FREQUENCY is more efficient/faster - is that why you chose it?

SUM(f)/ROWS(FILTER(f,f>0)))

calculates the average of the counts.

An amazing piece of work. Thank you

 

[Eric W]

As you've figured out, the first part creates a variable named u which contains all the rows of the table in the same month and year as the date in F6. The UNIQUE removes rows where the same patient comes in on the same day.

INDEX(u,0,1) is a way to get just the first column of the array. u is the array, 1 is the column we want, and the second parameter is the row that we want. But if we use 0, that tells INDEX to take all of the rows. So now we have a list of dates where we had patients. If there is more than 1 patient for a day, the date is in there more than once.

Now we need to count how many times each day of the month appears in the array. As you noted, COUNTIF only works on ranges, not arrays. FILTER might work, but I think we'd have to do it 31 times, for each day of the month. FREQUENCY does all the sorting for all the days at a time. The fact that it's pretty efficient is just a bonus. I use SEQUENCE to create a list of all the days in the month. These are the "buckets" that FREQUENCY will fill. Yes, it's possible if we have a month with 28 or 30 days, we'll end up including a few days of the next month. But since we've already restricted the input array to days in the current month, all this means is that we'll have a few buckets on the end that will have 0 entries.

So now we have a list of patient-days, a list of days in the month, and FREQUENCY does its magic and fills each bucket with the number of patients we saw on that day. So now we take the array that came out of FREQUENCY and calculate the average, which you've also figured out.

I played around with it a little more and got it a tad shorter, although the logic is pretty much the same:

Book3
	F	G
5	Month	Average patients per day
6	Aug 2021	1
7	Sep 2021	5
Sheet15

Cell Formulas
Range	Formula
F6:F7	F6	=UNIQUE(Table1[Date]-DAY(Table1[Date])+1)
G6:G7	G6	=LET(u,UNIQUE(FILTER(Table1,(Table1[Date]>=F6)*(Table1[Date]<=EOMONTH(F6,0)))),f,FREQUENCY(INDEX(u,0,1),SEQUENCE(31,,F6)),SUM(f)/SUM(SIGN(f)))
Dynamic array formulas.

Hope this helps!

 

[kcmuppet]

That's fantastic Eric.

About the modifications, EOMONTH, I understand, but what's going on with the SUM(f)/SUM(SIGN(f)))?

 

[Eric W]

Let's say we have an array like this {0;1;0;0;3;5}. The previous way I filtered out the 0s, leaving {1;3;5}. Then ROWS just said I have 3 rows in the array. SIGN returns a -1 if the number is negative, 0 if the number is 0, or 1 if the number is positive. So if you do SIGN({0;1;0;0;3;5}), it returns {0;1;0;0;1;1} and when you SUM that up, you get 3. The way FREQUENCY works, we know we don't have any negative numbers to worry about.

There are 2 or 3 other ways to count non-zero elements in an array. Here's another:

SUM(--(f<>0))

This would return {FALSE;TRUE:FALSE;FALSE;TRUE:TRUE} for the above array. Then the -- converts that to {0;1;0;0;1;1} and the SUM gives us 3 again.

SUM(IF(f<>0,1))

also works. I don't know if there's any real reason to choose one way or another. I've seen people do all of these, I think it comes down to personal preference. I've thought about trying to measure the performance difference for each method, but I haven't gotten around to that yet.

 

[kcmuppet]

That's very helpful, thank you for taking the time to help us all learn

 

[Eric W]

I kind of hate to keep going on when we have a working solution, but it occurred to me that from an algebraic point of view, since we're calculating an average, we don't need to break the counts into days. In other words, the FREQUENCY splits the counts into days, but the SUM(f) puts them right back together again. Given that, I came up with a still shorter formula:

=LET(u,INDEX(UNIQUE(FILTER(Table1,(Table1[Date]>=F6)*(Table1[Date]<=EOMONTH(F6,0)))),0,1),ROWS(u)/ROWS(UNIQUE(u)))

Anyway, I'm done with this now! Glad I could help, and thanks for the feedback!