Frequency IFS (not bin)

[hwkeyser]

Hello everyone,

I am currently working on an advance-for-me function to calculate a really variable average. I am basing it on the the Countifs/sum style of averages. I have managed to create a successful array formula for the countifs portion of the function, but i am struggling to develop the formula to divide it by. Here is why:


I am dividing this count if:

{=COUNTIFS('All Data'!$D:$D,Sheet7!B$1,'All Data'!U:U,Sheet7!$A3:$A25)}

by the unique frequency of a value

=SUM(IF(FREQUENCY(data,bins)>0,1))

My issue is, the unique frequency i need to divide by is specific to the same criteria used to determine the countifs.


I am leaving for the day, but in short -

countifs( total 'this person' surveyed 'this area')/sum(if(frequency( 'the number of unique days this person surveyed that area')

= Average laps per day that this person completes per route


any help would be amazing. I will be available to answer more questions upon reply.

Thanks!

Henry

 

[barry houdini]

Hello Henry,

"this area" seems to be represented by a range of cells in your COUNTIFS function, i.e. Sheet7!$A3:$A25 - is that right? Are there multiple locations that comprise one area? If so try this formula

=SUM(COUNTIFS('All Data'!$D2:$D1000,Sheet7!B$1,'All Data'!U2:U1000,Sheet7!$A3:$A25))/SUM(IF(FREQUENCY(IF('All Data'!$D2:$D1000=Sheet7!B$1,IF(ISNUMBER(MATCH('All Data'!U2:U1000,Sheet7!$A3:$A25,0)),IF('All Data'!$Z2:$Z1000<>"",MATCH('All Data'!$Z2:$Z1000,'All Data'!$Z2:$Z1000,0)))),ROW('All Data'!$Z2:$Z1000)-ROW('All Data'!$Z2)+1),1))

confirmed with CTRL+SHIFT+ENTER

Assumes data in rows 2 to 1000 with dates in column Z of All Data sheet, adjust as required

 

[hwkeyser]

I adjusted your formula for dates in row G and left it rows 2:1000 for testing (data extends to 60000).

The formula returns DIV/0 when entered as a corresponding array.

I am including two screenshots so you might be able to better help:

The report sheet

The raw data

Also: Here is the formula as I adjusted it (in case i made a mistake), because i'm not one hundred percent sure on what it does step-by-step.

{=SUM(COUNTIFS('All Data'!$D2:$D1000,Sheet7!B$1,'All Data'!U2:U1000,Sheet7!$A3:$A25))/SUM(IF(FREQUENCY(IF('All Data'!$D2:$D1000=Sheet7!B$1,IF(ISNUMBER(MATCH('All Data'!U2:U1000,Sheet7!$A3:$A25,0)),IF('All Data'!$G2:$G1000<>"",MATCH('All Data'!$G2:$G1000,'All Data'!$G2:$G1000,0)))),ROW('All Data'!$G2:$G1000)-ROW('All Data'!$G2)+1),1))}

 

[hwkeyser]

Hello Henry,

"this area" seems to be represented by a range of cells in your COUNTIFS function, i.e. Sheet7!$A3:$A25 - is that right? Are there multiple locations that comprise one area? If so try this formula

=SUM(COUNTIFS('All Data'!$D2:$D1000,Sheet7!B$1,'All Data'!U2:U1000,Sheet7!$A3:$A25))/SUM(IF(FREQUENCY(IF('All Data'!$D2:$D1000=Sheet7!B$1,IF(ISNUMBER(MATCH('All Data'!U2:U1000,Sheet7!$A3:$A25,0)),IF('All Data'!$Z2:$Z1000<>"",MATCH('All Data'!$Z2:$Z1000,'All Data'!$Z2:$Z1000,0)))),ROW('All Data'!$Z2:$Z1000)-ROW('All Data'!$Z2)+1),1))

confirmed with CTRL+SHIFT+ENTER

Assumes data in rows 2 to 1000 with dates in column Z of All Data sheet, adjust as required

You are correct, the Area for the sumfrequency calculation is dependent upon the countifs criteria. There are hundreds of combinations all mixed in together.

The three calculating criteria over the entire function are: Agent, Route, Date.
Was row this Agent on This route? Than what date did they do said row? Average rows based only on the number of unique days they worked that particular route.

Each row counts as one particular stop on a route.
The report is calculating what the average stops completed are for per agent per route.

ie: Agent A on average covers 302 stops per day that he works route S1-R1 while Agent B on average covers 270 stops per day that she works route S1-R1.