Spreadsheet: 859754d1708010544-dynamic-named-range-last-x-number-of-items-goal-dashboard
Sheet: Input

 | Strategic Planning [NAME] Sheet |  |  |  |  |  |  |  |  |  | KA01
 |  |  | Input the actual results for the last two years and the current year as it progresses.
 | 36 MONTH ACTUAL RESULTS |  | 2021-01-01 00:00:00
 | KEY AREAS |  | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual | Actual
1 | KA01 |  | 0.0047 | 0.0016 | 0.0026 | 0.0026 | 0.0034 | 0.0022 | 0.0018 | 0.0021 | 0.002 | 0.0018 | 0.0018 | 0.0023 | 0.0056 | 0.0075 | 0.0015 | 0.0013 | 0.002 | 0.0022 | 0.0037 | 0.0045 | 0.0035 | 0.0039 | 0.0038 | 0.0039 | 0.0058 | 0.0052 | 0.0039 | 0.0044 | 0.0054 | 0.0052 | 0.0056 | 0.0062 | 0.0062 | 0.0062 | 0.0059 | 0.0059
2 | KA02 |  | 0.7934 | 0.8656 | 0.7892 | 0.7899 | 0.7808 | 0.7827 | 0.7938 | 0.7798 | 0.7791 | 0.7881 | 0.7876 | 0.787 | 0.9503 | 0.8697 | 0.791 | 0.7999 | 0.7742 | 0.7716 | 0.7327 | 0.7305 | 0.7442 | 0.7336 | 0.7335 | 0.756 | 0.726 | 0.7241 | 0.7588 | 0.7425 | 0.7151 | 0.718 | 0.708 | 0.6942 | 0.6834 | 0.6871 | 0.6928 | 0.6928
3 | KA03 |  | 0.1332 | 0.1329 | 0.1327 | 0.1324 | 0.133 | 0.1329 | 0.1336 | 0.1339 | 0.1339 | 0.1328 | 0.1338 | 0.1338 | 0.1347 | 0.1345 | 0.134 | 0.1348 | 0.1344 | 0.1346 | 0.1357 | 0.1372 | 0.1373 | 0.1384 | 0.1366 | 0.1385 | 0.1401 | 0.1397 | 0.1391 | 0.1416 | 0.1422 | 0.1437 | 0.1459 | 0.1473 | 0.1481 | 0.1491 | 0.1477 | 0.1477
4 | [NAME] |  | 0.0009 | 0.0014 | 0.0055 | 0.005 | 0.0042 | 0.0034 | 0.0031 | 0.0033 | 0.0028 | 0.0027 | 0.0026 | 0.0039 | 0.0021 | 0.0027 | 0.0026 | 0.0014 | 0.0012 | 0.0011 | 0.0012 | 0.0011 | 0.0011 | 0.001 | 0.0024 | 0.0032 | 0.0099 | 0.009 | 0.0065 | 0.0055 | 0.0045 | 0.0043 | 0.0037 | 0.0044 | 0.0052 | 0.0048 | 0.0048 | 0.0048
5 | KA05 |  | 0.4393 | 0.4479 | 0.4425 | 0.4387 | 0.4349 | 0.4372 | 0.435 | 0.4394 | 0.4345 | 0.4434 | 0.4323 | 0.4283 | 0.4334 | 0.4296 | 0.4309 | 0.4298 | 0.4317 | 0.4399 | 0.4485 | 0.4536 | 0.4511 | 0.4533 | 0.4472 | 0.4548 | 0.4646 | 0.4633 | 0.4572 | 0.4677 | 0.4695 | 0.4801 | 0.5024 | 0.5113 | 0.5094 | 0.5126 | 0.5047 | 0.5047
6 | KA06 |  | 0.0978 | 0.0587 | 0.0548 | 0.0533 | 0.041 | 0.0308 | 0.0167 | 0.0151 | 0.0148 | 0.0227 | 0.0137 | 0.0132 | -0.0322 | 0.0263 | 0.0071 | -0.0115 | 0.0051 | 0.0056 | 0.0044 | -0.0095 | -0.0074 | -0.0115 | 0.0067 | -0.0054 | -0.1003 | 0.003 | 0.0246 | -0.0147 | 0.0056 | -0.0065 | -0.0164 | -0.0115 | -0.0067 | -0.0069 | 0.0089 | 0.0089
7 | KA07 |  | -0.0782 | 0.0895 | 0.0435 | 0.0048 | -0.0184 | -0.0047 | -0.026 | -0.0088 | -0.0289 | -0.0204 | -0.0214 | -0.0274 | 0.1065 | 0.0409 | 0.0386 | 0.0068 | 0.031 | 0.066 | 0.0877 | 0.0899 | 0.0667 | 0.0557 | 0.0487 | 0.0549 | 0.1284 | 0.104 | 0.0422 | 0.0611 | 0.0755 | 0.0958 | 0.1535 | 0.1609 | 0.1425 | 0.1347 | 0.1216 | 0.1216
8 | KA08 |  | 0.0047 | 0.0071 | 0.0024 | 0.0018 | -0.0005 | -0.0016 | -0.0013 | 0.0006 | 0.0005 | -0.0031 | -0.0028 | -0.0031 | -0.0401 | -0.0106 | -0.0126 | -0.0106 | -0.0123 | -0.013 | -0.0091 | -0.0091 | -0.0118 | -0.0165 | -0.018 | -0.0169 | -0.0168 | -0.012 | -0.008 | -0.006 | -0.0091 | -0.0072 | -0.0058 | -0.0102 | -0.0125 | -0.0218 | -0.017 | -0.017
9 | KA09 |  | 0.0546 | 0.0436 | 0.0448 | 0.0462 | 0.0463 | 0.0462 | 0.0462 | 0.0462 | 0.0461 | 0.046 | 0.046 | 0.0463 | 0.0457 | 0.0432 | 0.0449 | 0.0444 | 0.0444 | 0.0442 | 0.044 | 0.0507 | 0.0443 | 0.0448 | 0.0448 | 0.0445 | 0.0454 | 0.0433 | 0.0432 | 0.0432 | 0.0438 | 0.0434 | 0.0433 | 0.0435 | 0.0439 | 0.0444 | 0.0445 | 0.0445
10 | KA10 |  | 0.1047 | 0.0642 | 0.0763 | 0.0556 | 0.0426 | 0.0356 | 0.0167 | 0.0139 | 0.0058 | -0.0132 | 0.0127 | 0.0128 | -0.0344 | 0.0227 | 0.0145 | -0.0038 | 0.0117 | 0.0119 | 0.0068 | -0.0102 | -0.0037 | -0.0134 | 0.0006 | -0.0065 | -0.1291 | -0.0086 | 0.0206 | -0.0235 | -0.0021 | -0.0146 | -0.0231 | -0.0227 | -0.0155 | -0.0158 | 0.0017 | 0.0017
11 | KA11
12 | KA12
13 | [NAME]
14 | KA14
15 | KA15
 |  |  | Input current year Goals only. [NAME] will be constant for the year.
 | GOAL ESTABLISHED IN PLANNING
 | KEY AREAS |  | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal | Goal
1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.0035
2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.7
3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.13
4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.01
5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.5
6 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
7 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.07
8 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.0025
9 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.0475
10 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
11
12
13
14
15
 |  |  | Input current year Preferred Ranges only. Ranges will remain constant for the entire year
 | PREFERRED RANGE
 | KEY AREAS |  | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range | Range
1 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.0025
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.006
2 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.65
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.8
3 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.12
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.15
4 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.0075
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.015
5 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.45
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.55
6 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.01
7 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.05
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.1
8 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.01
9 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.045
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.05
10 |  | HIGH |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
 |  | LOW |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.01
11 |  | HIGH
 |  | LOW
12 |  | HIGH
 |  | LOW
13 |  | HIGH
 |  | LOW
14 |  | HIGH
 |  | LOW
15 |  | HIGH
 |  | LOW