Jules_Excel
New Member
- Joined
- Feb 13, 2021
- Messages
- 5
- Office Version
- 2019
- Platform
- Windows
Hi folks,
I've been tasked to create a spreadsheet which looks at the current month and sums the number of incidents in either a single range, or an area range, depending on a set of applied rules, based on a start point of the current month and looking back either 6 months, or 12 months, depending on which rule is being applied.
The data area will expand back 24 months, so as to cover the whole of the current year and of the previous year, but only a sufficient count within the scope of the rule-defined ranges should trigger the logic state to change. My example method (provided) broke on a 24 month history, as the logic kept triggering out of scope of the rules, so I really could use a better way of doing this.
I've bodged together a semi-working example covering just 1 year.
The rules are as follows:
#1: Count 2 or more under any singular category in any 6 months = Discuss
#2: Count 3 or more under any combined categories in any 6 months = Discuss
#3: Count 4 or more under any combined categories in any 12 months = Alert
#4: Any additional count over 4 in any category in any 12 months = Strong Alert
What I want to do to plot the area range (Please note: This is not how I've done it in the example sheet- this is the part I need help with):
- The start column position is given by a match based on the current month.
Note: This month value is currently set to a static value, but no need to worry about that.
- The starting row position is offset down by 2 to put the start point in the data area
- The end row range expands down 8 to the bottom of the data area
- The end column range looks left by 6, or 12, depending on the rule being applied
- The trigger logic is determined by each rule, depending on the sum in the defined area/range
Feel free to scrap my method entirely! - I'm really looking for a better, slicker example method of how to achieve this.
Thanks in advance!
I've been tasked to create a spreadsheet which looks at the current month and sums the number of incidents in either a single range, or an area range, depending on a set of applied rules, based on a start point of the current month and looking back either 6 months, or 12 months, depending on which rule is being applied.
The data area will expand back 24 months, so as to cover the whole of the current year and of the previous year, but only a sufficient count within the scope of the rule-defined ranges should trigger the logic state to change. My example method (provided) broke on a 24 month history, as the logic kept triggering out of scope of the rules, so I really could use a better way of doing this.
I've bodged together a semi-working example covering just 1 year.
The rules are as follows:
#1: Count 2 or more under any singular category in any 6 months = Discuss
#2: Count 3 or more under any combined categories in any 6 months = Discuss
#3: Count 4 or more under any combined categories in any 12 months = Alert
#4: Any additional count over 4 in any category in any 12 months = Strong Alert
What I want to do to plot the area range (Please note: This is not how I've done it in the example sheet- this is the part I need help with):
- The start column position is given by a match based on the current month.
Note: This month value is currently set to a static value, but no need to worry about that.
- The starting row position is offset down by 2 to put the start point in the data area
- The end row range expands down 8 to the bottom of the data area
- The end column range looks left by 6, or 12, depending on the rule being applied
- The trigger logic is determined by each rule, depending on the sum in the defined area/range
Feel free to scrap my method entirely! - I'm really looking for a better, slicker example method of how to achieve this.
Thanks in advance!