Hi everyone,
Sorry if I am overlooking other resources on this forum regarding a similar question.
I am currently using a sum-frequency array formula to calculate how many unique police precincts:
1) have operational interventions in a given year; and
2) see a decrease in burglaries from one year to the next.
Please note the following about the police precincts:
1) it is possible that there could be smaller intervention sites in a police precinct, which could have operational or inoperable interventions at the end of each year; and
2) the # of burglaries might only be reported at the level of a police precinct (which you can think of as the largest police jurisdiction type). This means that smaller intervention sites, in certain cases, won't have any burglary data because the police force can only report for all intervention sites at the level of a police precinct.
The array formula that I have drafted thus far is counting the unique police precincts per year correctly (when that is the only criteria in the formula)... but not the # of unique precincts that see a decrease in burglaries from one year to the next. Instead, the array formula seems to look at the # of burglaries in precincts with interventions for a given year, aggregates the # of burglaries for that year, and then compares that result to the # of burglaries for the next year. However, this does provide the correct result.
I have pasted what the raw data looks like for two police precincts below.
This is the raw data, for Precinct #1:
This is the raw data, for Precinct #2:
I am pasting different versions of the array formulas (which are more or less the same from one another) below. The arrays formula looks at all the raw data provided above in order to calculate the result. The word "DCT" in each array formula below is just the name of the tab (which I'm sure that you already knew/could have surmised).
Array #1:
=SUM(--(FREQUENCY(IF((DCT!$C$2:$C$9997<>"")*(DCT!$A$2:$A$9997="CY2016")*(DCT!$E$2:$E$9997="Operational")*(DCT!$F$2:$F$9997>=0)*(DCT!$F$2:$F$9997<>"")*(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")),MATCH(DCT!$C$2:$C$9997,DCT!$C$2:$C$9997,0)),ROW(DCT!$C$2:$C$9997)-ROW(DCT!$C$2)+1)>0))
Result from Array #1 for CY2016 = 2
Array #3:
=SUM(--(FREQUENCY(IF((DCT!$C$2:$C$9997<>"")*(DCT!$A$2:$A$9997="CY2016")*(DCT!$E$2:$E$9997="Operational")*(DCT!$F$2:$F$9997>=0)*(DCT!$F$2:$F$9997<>"")*(IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0"),"1","0")),MATCH(DCT!$C$2:$C$9997,DCT!$C$2:$C$9997,0)),ROW(DCT!$C$2:$C$9997)-ROW(DCT!$C$2)+1)>0))
Result from Array #2 for CY2016 = 2
The CY2016 result for each array formula, based on the raw data, should be 1, not 2. This is because Precinct #2 saw its burglaries decrease from CY2015 to CY2016, whereas Precinct #1 saw its burglaries increase in the same time period.
If it is not possible to have just *one* formula that calculates the result, that's fine. Similarly, if what I need to use is a completely different formula type, please direct me in the right direction.
Thanks!
Sorry if I am overlooking other resources on this forum regarding a similar question.
I am currently using a sum-frequency array formula to calculate how many unique police precincts:
1) have operational interventions in a given year; and
2) see a decrease in burglaries from one year to the next.
Please note the following about the police precincts:
1) it is possible that there could be smaller intervention sites in a police precinct, which could have operational or inoperable interventions at the end of each year; and
2) the # of burglaries might only be reported at the level of a police precinct (which you can think of as the largest police jurisdiction type). This means that smaller intervention sites, in certain cases, won't have any burglary data because the police force can only report for all intervention sites at the level of a police precinct.
The array formula that I have drafted thus far is counting the unique police precincts per year correctly (when that is the only criteria in the formula)... but not the # of unique precincts that see a decrease in burglaries from one year to the next. Instead, the array formula seems to look at the # of burglaries in precincts with interventions for a given year, aggregates the # of burglaries for that year, and then compares that result to the # of burglaries for the next year. However, this does provide the correct result.
I have pasted what the raw data looks like for two police precincts below.
This is the raw data, for Precinct #1:
This is the raw data, for Precinct #2:
I am pasting different versions of the array formulas (which are more or less the same from one another) below. The arrays formula looks at all the raw data provided above in order to calculate the result. The word "DCT" in each array formula below is just the name of the tab (which I'm sure that you already knew/could have surmised).
Array #1:
=SUM(--(FREQUENCY(IF((DCT!$C$2:$C$9997<>"")*(DCT!$A$2:$A$9997="CY2016")*(DCT!$E$2:$E$9997="Operational")*(DCT!$F$2:$F$9997>=0)*(DCT!$F$2:$F$9997<>"")*(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")),MATCH(DCT!$C$2:$C$9997,DCT!$C$2:$C$9997,0)),ROW(DCT!$C$2:$C$9997)-ROW(DCT!$C$2)+1)>0))
Result from Array #1 for CY2016 = 2
Array #3:
=SUM(--(FREQUENCY(IF((DCT!$C$2:$C$9997<>"")*(DCT!$A$2:$A$9997="CY2016")*(DCT!$E$2:$E$9997="Operational")*(DCT!$F$2:$F$9997>=0)*(DCT!$F$2:$F$9997<>"")*(IF(SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2015",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0")>SUMIFS(DCT!$F$2:$F$9997,DCT!$A$2:$A$9997,"CY2016",DCT!$E$2:$E$9997,"Operational",DCT!$F$2:$F$9997,">=0"),"1","0")),MATCH(DCT!$C$2:$C$9997,DCT!$C$2:$C$9997,0)),ROW(DCT!$C$2:$C$9997)-ROW(DCT!$C$2)+1)>0))
Result from Array #2 for CY2016 = 2
The CY2016 result for each array formula, based on the raw data, should be 1, not 2. This is because Precinct #2 saw its burglaries decrease from CY2015 to CY2016, whereas Precinct #1 saw its burglaries increase in the same time period.
If it is not possible to have just *one* formula that calculates the result, that's fine. Similarly, if what I need to use is a completely different formula type, please direct me in the right direction.
Thanks!
