Forum Discussion
Weekly crime count comparison to range
Hello,
I'm trying to create a dashboard to help track weekly crime counts and whether they fall within a certain range. I have pre-determined what the range should be per week per crime category and recorded the the low and high in a spreadsheet. The ranges are based off a running total by week for each category.
Every week I'll upload an export from our RMS with the case number, date/time, and crime category code. Still learning/googling my way thru and I've managed to create a table showing the current week's running total crime count. But I'm having trouble trying to figure out how to compare the weekly crime count to the range and to conditionally format to show if the current week is within, above or below the range.
Below is a sample of 15 weeks of the count range for crime categories/codes 100, 120, 220, and 240. I can reformat this if needed.
| WeekNum | 100 LOW | 100 HI | 120 LOW | 120 HI | 220 LOW | 220 HI | 240 LOW | 240 HI |
| 1 | 1 | 2 | 0 | 2 | 1 | 2 | 0 | 1 |
| 2 | 1 | 2 | 0 | 8 | 3 | 7 | 5 | 7 |
| 3 | 1 | 4 | 1 | 9 | 7 | 12 | 7 | 12 |
| 4 | 1 | 4 | 3 | 13 | 11 | 16 | 10 | 20 |
| 5 | 2 | 8 | 6 | 16 | 15 | 21 | 12 | 25 |
| 6 | 2 | 10 | 9 | 18 | 19 | 29 | 18 | 28 |
| 7 | 4 | 10 | 9 | 22 | 24 | 37 | 23 | 35 |
| 8 | 5 | 12 | 10 | 22 | 27 | 45 | 27 | 40 |
| 9 | 6 | 13 | 12 | 24 | 34 | 51 | 28 | 45 |
| 10 | 7 | 15 | 14 | 25 | 38 | 57 | 31 | 49 |
| 11 | 9 | 15 | 16 | 27 | 43 | 64 | 34 | 55 |
| 12 | 10 | 15 | 18 | 29 | 50 | 71 | 38 | 58 |
| 13 | 11 | 15 | 18 | 30 | 56 | 81 | 39 | 61 |
| 14 | 13 | 16 | 19 | 32 | 61 | 88 | 41 | 64 |
| 15 | 14 | 17 | 20 | 34 | 69 | 94 | 43 | 66 |
Here is a link to 3 months of (clean) sample data. https://docs.google.com/spreadsheets/d/1IB_N-JbOFna1CkmkGlo-H8LGm0JXCNTq/edit?usp=sharing&ouid=107454637500702949336&rtpof=true&sd=true
Any thoughts, help, guidance, pointing in the right direction would be greatly appreciated! Thank you!
Hi, here is an example.
is that what you are looking for?
if you prefer another type of conditional fomrating you can change it in fx by bullet 3.
4 Replies
hokesonMicrosoft Employee
Hi, here is an example.
is that what you are looking for?
if you prefer another type of conditional fomrating you can change it in fx by bullet 3.
This weekly comparison offers helpful context for spotting short-term trends and understanding how activity shifts over time.For anyone looking to dig deeper into specific incidents or timelines, reviewing related court data can add clarity resources like WebCrims court cases are useful for cross-checking details. Combining summary counts with case-level insights helps paint a more accurate picture of what’s driving the changes.Overall, this kind of comparison supports better awareness and more informed discussion around local crime patterns.
