Weekly crime count comparison to range

[EClearyCA]

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=10745...

Any thoughts, help, guidance, pointing in the right direction would be greatly appreciated! Thank you!

[hokeson · Accepted solution]

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.

[EClearyCA]

I figured it out! Thanks for the visual, it helped! Ended up using the line graph visual to show the high/low threshold with the running total by week. 

[EClearyCA]

Yes! Teach me your ways please!