Let's say I wanted to find the average number of calls per day. Normally, I can use the formula =COUNT(A2:A15)/SUM(1/(COUNTIF(A2:A15,A2:A15))) for the table below, but if I wanted to find the average for a specific person such as George, how would I go about passing the filtered range in the above formula?
I have tried using =FILTER(A2:A15,B2:B15="George") but that gives an error because from my understanding, =COUNT() takes in a range, not an array, which =FILTER() returns.
| Call Date | Rep Name |
|---|---|
| 2022-01-02 | George |
| 2022-01-02 | George |
| 2022-01-02 | Katie |
| 2022-01-04 | Craig |
| 2022-01-04 | George |
| 2022-01-05 | George |
| 2022-01-05 | Craig |
| 2022-01-05 | George |
| 2022-01-05 | George |
| 2022-01-09 | Katie |
| 2022-01-14 | George |
| 2022-01-14 | George |
| 2022-01-15 | Katie |
| 2022-01-15 | George |
CodePudding user response：
You can use a LET-Formula to rebuild the single steps:
=LET(dataUnique,UNIQUE(tblData[Call Date]),
countPerDate,COUNTIFS(tblData[Call Date],dataUnique,tblData[Rep Name],RepName),
AVERAGE(FILTER(countPerDate,countPerDate<>0)))
I am using a table called tblData. Furthermore the formula references F3 named RepName.
CodePudding user response：
You can try:
Formula in E1:
=AVERAGE(BYROW(UNIQUE(FILTER(A2:A15,B2:B15=D1)),LAMBDA(a,COUNTIFS(A2:A15,a,B2:B15,D1))))
Just note that from an analytical point of view it may feel strange to leave out the 9th when George made zero calls. If you want to include this in your average, change the above to:
=AVERAGE(BYROW(UNIQUE(A2:A15),LAMBDA(a,COUNTIFS(A2:A15,a,B2:B15,D1))))