Calculating the sum of a range or an area range where the start point can move

Jules_Excel

New Member
Joined
Feb 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. 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!
 

Attachments

  • Screenshot (2).png
    Screenshot (2).png
    123.8 KB · Views: 14

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
Welcome to the board! Could you say a little more about the scope of this system, please? Your example shows one person and eight categories. Approximately how many people would be tracked with this system? And are the categories and number of categories the same for each person?
 

Jules_Excel

New Member
Joined
Feb 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Thank you!

Sure. In response to your questions:

- There would be roughly 60 people tracked. The intention is to simply copy/paste a complete instance of the working chart in its entirety, for each individual, going down the worksheet.
- The number of categories are the same for each individual.

I appreciate you taking the time to have a look. TY!
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
Thanks for the clarification. I have a working example to try out, and perhaps you can let me know a little more about the rule sets. In this example, there are three table areas.
  1. The main display table that looks similar to the one you described in your first post.
  2. An incident data table located below the main display table. This data table is the one where monthly updates are made to record the number of incidents per category for the individual. There are no formulas in the data table. I've set up this table in the range A17:I139 to cover 10 years of reporting.
  3. A helper computation table located to the right of the main display table. This is where the number of incidents occurring in various 6-month or 12-month blocks are tabulated. Additionally, this helper computation table contains the rule sets for Rules 1, 2, 3, and 4.
In operation, the user inputs the Evaluation Date in the blue cell AC9. This date is used to determine begin and end dates for the two-year period that contains the Evaluation Date as month number 24. And the two-year calendar labels in the main display table update based on these begin and end dates. Then formulas in the main data table match the category name and Month-Year heading to pull the corresponding number of incidents from the incident data table. The helper computation table located to the far right sums the incidents according to the rule sets you've described, although some additional clarification is needed (more on this below). The final results of the four rules are shown in cells AE13:AF16...these results are pulled into the main display table in cells Z4:AC4. Since Rule 1 involves evaluations against singular categories, the detailed Rule 1 results are found in AE5:AF12 and then pulled into the main display table in cells Z5:Z12. Conditional formatting is then applied to the summary rule results in Z4:AC4 based on the text in those cells.

With regard to the rules, I am not clear on whether these rules apply to the full two-year history or only a portion of it. This version assumes that the rules apply to all contiguous 6-month or 12-month blocks within the 2-year period that ends with the month-year of the Evaluation Date. Let me know if this is not correct.

The preferred way to share working examples is using the XL2BB add-in. To copy my sheet to your workbook, click on the clipboard icon in the upper left at the intersection of the row/column headings and then go to a blank worksheet and pasted into the same cell shown in the upper left of the posted example (cell A1 in this case).

The main display table and helper computation table are shown here (pasted in cell A1).
MrExcel20210214.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
1Month #123456789101112131415161718192021222324Begin3/1/2019
2Mo-YrM-19A-19M-19J-19J-19A-19S-19O-19N-19D-19J-20F-20M-20A-20M-20J-20J-20A-20S-20O-20N-20D-20J-21F-21End2/28/2021
3Name:Rule 1Rule 2Rule 3Rule 4
4Joe BloggsDiscussDiscussAlertStrong Alert6-mo blocks -->12345678910111213141516171819Rule description
5Category 1100000000000000000001230DiscussRule 1Discuss1000000000000001366
6Category 2001000000001000000000000 Rule 1 1110001111110000000
7Category 3000000020000000001000000DiscussRule 1Discuss0022222200001111110
8Category 4000001000000001000000000 Eval DateRule 1 1111110001111110000
9Category 5000000000020000020000000Discuss2/13/2021Rule 1Discuss0000022222222222200
10Category 6000000100000000000100000 Rule 1 0111111000000111111
11Category 7000000010000000000001000 Rule 1 0011111100000001111
12Category 8000000002000000000000002DiscussRule 1Discuss0002222220000000002
13Rule 1Discuss>=2 any 6 mo sing cat
14Rule 2Discuss33677998544445568910>=3 any 6 mo comb cat
15Rule 3Alert12111111111313131010121314>=4 any 12 mo comb cat
16Rule 4Strong Alert3344444444588>4 any 12 mo sing cat
Jules_Excel
Cell Formulas
RangeFormula
B2:Y2B2=INDEX($A$20:$A$139,MATCH($AF$1,$A$20:$A$139,0)+COLUMNS($B2:B2)-1)
AF1AF1=DATE(YEAR($AC$9)-2,MONTH($AC$9)+1,1)
AF2AF2=EOMONTH($AC$9,0)
Z4Z4=AF13
AA4AA4=AF14
AB4AB4=AF15
AC4AC4=AF16
B5:Y12B5=INDEX($B$20:$I$139,MATCH(B$2,$A$20:$A$139,0),MATCH($A5,$B$19:$I$19,0))
Z5:Z12Z5=AF5
AG5:AY12AG5=SUM(OFFSET($A5,{0;1;2;3;4;5;6;7},{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19},1,6))
AF5:AF12AF5=IF(COUNTIF(AG5:AY5,">="&2),"Discuss","")
AF13AF13=IF(COUNTIF(AF5:AF12,"Discuss"),"Discuss","Pass")
AF14AF14=IF(COUNTIF(AG14:AY14,">="&3),"Discuss","Pass")
AG14:AY14AG14=SUM(AG5:AG12)
AF15AF15=IF(COUNTIF(AG15:AS15,">="&4),"Alert","Pass")
AG15:AS15AG15=SUM(AG14,AM14)
AF16AF16=IF(COUNTIF(AG16:AS16,">"&4),"Strong Alert","Pass")
AG16:AS16AG16=SUM(MAX(AG5:AG12),MAX(AM5:AM12))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Z4:AC4Cell Valuecontains "Pass"textNO
Z4:AC4Cell Valuecontains "Discuss"textNO
Z4:AC4Cell Valuecontains "Strong Alert"textNO
Z4:AC4Cell Valuecontains "Alert"textNO
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The incident data table should be created on the same sheet as the main display table. In this case, I created it beginning in cell A17. The headings found in B19:I19 should remain, as the matching formula in the main display table utilizes these labels. You can, however, hide the row if desired. For that matter, you can hide any of the rows that have already been filled out to effectively bring the current month closer to the top of the table.
MrExcel20210214.xlsx
ABCDEFGHI
17Incident Table:
18Categories -->12345678
19keep labels -->Category 1Category 2Category 3Category 4Category 5Category 6Category 7Category 8
20Jan-19
21Feb-19
22Mar-191
23Apr-19
24May-191
25Jun-19
26Jul-19
27Aug-191
28Sep-191
29Oct-1921
30Nov-192
31Dec-19
32Jan-202
33Feb-201
34Mar-20
35Apr-20
36May-201
37Jun-20
38Jul-202
39Aug-201
40Sep-201
41Oct-20
42Nov-2011
43Dec-202
44Jan-213
45Feb-212
46Mar-21
47Apr-211
48May-21
49Jun-211
50Jul-211
51Aug-211
52Sep-21
53Oct-21
54Nov-211
55Dec-211
56Jan-22
57Feb-22
58Mar-22
59Apr-22
60May-22
61Jun-22
62Jul-22
63Aug-22
64Sep-22
65Oct-22
66Nov-22
67Dec-22
68Jan-23
69Feb-23
70Mar-23
71Apr-23
72May-23
73Jun-23
74Jul-23
75Aug-23
76Sep-23
77Oct-23
78Nov-23
79Dec-23
80Jan-24
81Feb-24
82Mar-24
83Apr-24
84May-24
85Jun-24
86Jul-24
87Aug-24
88Sep-24
89Oct-24
90Nov-24
91Dec-24
92Jan-25
93Feb-25
94Mar-25
95Apr-25
96May-25
97Jun-25
98Jul-25
99Aug-25
100Sep-25
101Oct-25
102Nov-25
103Dec-25
104Jan-26
105Feb-26
106Mar-26
107Apr-26
108May-26
109Jun-26
110Jul-26
111Aug-26
112Sep-26
113Oct-26
114Nov-26
115Dec-26
116Jan-27
117Feb-27
118Mar-27
119Apr-27
120May-27
121Jun-27
122Jul-27
123Aug-27
124Sep-27
125Oct-27
126Nov-27
127Dec-27
128Jan-28
129Feb-28
130Mar-28
131Apr-28
132May-28
133Jun-28
134Jul-28
135Aug-28
136Sep-28
137Oct-28
138Nov-28
139Dec-28
Jules_Excel
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
The helper computation table may be somewhat confusing. The table below might help explain what the formulas are doing. For a 24-month calendar, there are 19 sets of contiguous 6-month blocks and 13 sets of contiguous 12-month blocks, as shown in the two tables here. And if the 6-month blocks are processed first, one can derive the 12-month blocks by simply combining the correct two 6-month blocks, as summarized in cells A23:A35. The formulas essentially find these blocks and sum the incidents occurring within each.
MrExcel20210214.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
26-mo blks \ mos123456789101112131415161718192021222324
31111111
42111111
53111111
64111111
75111111
86111111
97111111
108111111
119111111
1210111111
1311111111
1412111111
1513111111
1614111111
1715111111
1816111111
1917111111
2018111111
2119111111
226-mo blks12-mo blks \ mos
231,71111111111111
242,82111111111111
253,93111111111111
264,104111111111111
275,115111111111111
286,126111111111111
297,137111111111111
308,148111111111111
319,159111111111111
3210,1610111111111111
3311,1711111111111111
3412,1812111111111111
3513,1913111111111111
Jules_Excel_explanation
 

Jules_Excel

New Member
Joined
Feb 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hello again,

Firstly, my apologies for taking so long in relying: I've been extremely busy. Secondly, thank you for taking the time for such a comprehensive response! Very much appreciated.

After a lot of digging around, I've decided on a solution that I feel best compliments how the end-users will be able to practically utilise the worksheet on an ongoing basis, in conjunction with the required functionality: there's a significant need for copying and pasting for each individual being monitored, so the more compact the solution, the better.

Solution
So, the table layout covers two years of data. This is all that's required, as the scope of the test range is, at most, 12 months. So as long as the previous years data exists to catch any overflow from early in the current year from the last year (for the 12 month test criteria), we're good to go.

Screenshot (4).png


Today's Date is set from the system using the TODAY() function. The Month is derived thereof, using MONTH(AC12) where AC12 is the cell containing the current date.

So now we know where we in the time-line - important, as I want to look from 'Now', back either 6 or 12 months depending on the Rule in question. To reference this, I've used the cells in row 2, starting on column N, with individual IF statements in each cell to place a numerical value that corresponds to the month; so 1 for Jan, 2 for Feb, and so on. So now each month knows its numerical position and I'll use this position as a start-point to calculate back the Rule-applicable 6 or 12 months range.

RULE #1:
Rule #1 is looking for an number of incidents greater than or equal to 2 in a single category

The matching for the Rule 1 single row ranges is achieved by:

=IF(SUM(OFFSET(N4,1,MATCH($AC$9,$N$2:$Y$2)-1,1,-6))>=2,1,0)

* The OFFSET references N4, which gives us an arbitrarily chosen starting position near the start of the data table.
The subsequent '1' indicates the row in which to look, with respect to the starting position mentioned above, in this case 'down 1 row', placing us in the left-uppermost row of the data table.
* The MATCH references the active month cell (currently populated with a '2' from the IF statement mentioned earlier. So now between those row and column coordinates, we've zeroed in on 'which row to look at' starting from which month.
* The -6 defines the 'previous 6 months from where we are currently'.
* And the SUM totals that row, and the IF looks for totals greater than or equal to 2.

Each of the helper cells directly under the Rule 1 column has a 0 or a 1 generated in it by the IF part of the statement, if the data row contains values equal to or greater than 2 in SUM.

The Rule 1 Pass/Fail reporting cell has an IF statement that looks for the occurrence of a 1 in any of the helper cells below, and changes state if a 1 is present, thereby neatly consolidating the reporting display to one cell.

RULE #2:
=IF(SUM(OFFSET(N4,1,MATCH($AC$9,$N$2:$Y$2)-1,8,-6))>=3,"Discuss","Pass")

Much the same method as described above, but the '8' defines an area range (as in: include all 8 rows, down from the column dated start position), as Rule 2 is looking for any incidents for any of the categories in a 6 month period, totalling equal to or greater than 3 occurrences.

RULE #3:
=IF(SUM(OFFSET(N4,1,MATCH(AC9,N2:Y2)-1,8,-12))>=4,"Alert","Pass")

The same as Rule #2, but with a 12 month area range now instead of 6.

RULE #4:
=IF(SUM(OFFSET(N4,1,MATCH(AC9,N2:Y2)-1,8,-12))>4,"Strong Alert","Pass")

... And so on for Rule #4, excepting that the IF condition has a higher trigger threshold.

Finally, at the end of the current year, the end-user will simply copy/paste this years data table values into last years table, and clear the data from the now, new current year, for each individual.


Thanks again for all your time and effort you've put into your example.
 
Solution

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
Thanks for the update. I'm glad you were able to arrive at a solution. Your explanation helped to clear up some questions I had. In post #1, you mentioned "looking back either 6 months, or 12 months" but later mentioned "in any 6 months" or "in any 12 months". I approached the problem as though you might need an assessment for the latter--in any 6 or 12 month block depending on the rule. It appears that you really want only one particular 6 or 12 month block...the ones ending in the current month...which would be the answer to a question I posed in post #4.

I am somewhat puzzled by your approach to Rule #4. In post #1, you mentioned "Any additional count over 4 in any category in any 12 months". My interpretation of "any category" was any singular category, while your formula is summing over all categories, just like Rule #3, but applying a slightly higher incident count threshold (>4 rather than >=4). I assumed, based on the post #1 description, that Rule #4 would look for the maximum value (not the sum) in each category within the 12-month block of interest.
 

Jules_Excel

New Member
Joined
Feb 13, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Thanks for the update. I'm glad you were able to arrive at a solution. Your explanation helped to clear up some questions I had. In post #1, you mentioned "looking back either 6 months, or 12 months" but later mentioned "in any 6 months" or "in any 12 months". I approached the problem as though you might need an assessment for the latter--in any 6 or 12 month block depending on the rule. It appears that you really want only one particular 6 or 12 month block...the ones ending in the current month...which would be the answer to a question I posed in post #4.

I am somewhat puzzled by your approach to Rule #4. In post #1, you mentioned "Any additional count over 4 in any category in any 12 months". My interpretation of "any category" was any singular category, while your formula is summing over all categories, just like Rule #3, but applying a slightly higher incident count threshold (>4 rather than >=4). I assumed, based on the post #1 description, that Rule #4 would look for the maximum value (not the sum) in each category within the 12-month block of interest.
Yes, you are correct: I assert 'Any' as in 'All'. Semantically applicable, although now you mention it I see that 'All' might have been a clearer term.

I again use 'Any' in application to Rule #4 in the same context.

Glad we cleared that up! :)

Thanks again.
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
With regard to 6-month and 12-month blocks of time, post #1 uses the word "any" in all four rules. This implies that there are multiple time blocks, and that the rules apply to any of them. The problem statement is not clear about the fact that only one block of time is of interest---the one ending in the current month. It is now apparent that the intent was to convey that for any given input date, the assessment should consider only the past 6-month or 12-month block of time ending in the month of the input date, depending on the rule.

With regard to categories, the inconsistency remains. In post #1, Rule #1 refers to a "singular category", Rule #2 and Rule #3 both refer to "combined categories", and Rule #4 refers to "any category". The terms "singular" and "combined" are clear, but then "any category" is introduced in Rule #4. This means a single category, not multiple ones, as "category" is singular. Yet based on your solution, it appears that the intent was to convey that Rule #4 applies to combined categories, just like Rule #3.

The point is that the word "any" does not mean "all" and that the problem statement could have been clearer to avoid this confusion.
 

Forum statistics

Threads
1,141,122
Messages
5,704,431
Members
421,349
Latest member
Santhosh3188

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top