Spreadsheet: 36597d1234464191-using-list-function-with-hiding-irrelevant-outputs-monthly-models
Sheet: Staff Number Calculation

 | Select Year | Annualized Year
Branch 26 | AS IS
Work Items | Transactions Received to be Processed | Est. Processing (Min Time in Hours) - YTD | Est. Processing (Max Time in Hours) - YTD | Est. Processing (Average Time in Hours) - YTD | Est. Min FTEs Required 2008 | Est. [NAME] 2008 | Est. Average FTEs Required 2008
A | 1200 | 40 | 60 | 80 | 0.4444444444444444 | 0.6666666666666666 | 0.8888888888888888
B | 1200 | 60 | 80 | 100 | 0.6666666666666666 | 0.8888888888888888 | 1.111111111111111
C | 1200 | 80 | 100 | 120 | 0.8888888888888888 | 1.111111111111111 | 1.333333333333333
Output |  | Est. Processing (Min Time in Hours) - YTD | Est. Processing (Max Time in Hours) - YTD | Est. Processing (Average Time in Hours) - YTD | Est. Min FTEs Required 2008 | Est. [NAME] 2008 | Est. Average FTEs Required 2008
Total Staff Required (Annualized) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Annualized Year Option is selected in C 3
Total Staff Required (1st Quarter Only) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if 1st Quarter Only option is selected in C3 plus should only calculate volumes for Jan, Feb and Marc 2008 from worksheet named "Volumes"
Total Staff Required (2nd Quarter Only) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if 2nd Quarter Only option is selected in C3 plus should only calculate volumes for Apr, May, and Jun 2008 from worksheet named "Volumes"
Total Staff Required (3rd Quarter Only) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if 3rd Quarter Only option is selected in C3 plus should only calculate volumes for Jul, Aug, and Sep 2008 from worksheet named "Volumes"
Total Staff Required (4th Quarter Only) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if 4th Quarter Only option is selected in C3 plus should only calculate volumes for Oct, Nov, and Dec 2008 from worksheet named "Volumes"
Total Staff Required (Jan 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Jan 2008 from worksheet named "Volumes"
Total Staff Required (Feb 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Feb 2008 from worksheet named "Volumes"
Total Staff Required (Mar 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Mar 2008 from worksheet named "Volumes"
Total Staff Required (Apr 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Apr 2008 from worksheet named "Volumes"
Total Staff Required (May 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for May 2008 from worksheet named "Volumes"
Total Staff Required (Jun 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Jun 2008 from worksheet named "Volumes"
Total Staff Required (Jul 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Jul 2008 from worksheet named "Volumes"
Total Staff Required (Aug 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Aug 2008 from worksheet named "Volumes"
Total Staff Required (Sep 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Sep 2008 from worksheet named "Volumes"
Total Staff Required (Oct 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Oct 2008 from worksheet named "Volumes"
Total Staff Required (Nov 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Nov 2008 from worksheet named "Volumes"
Total Staff Required (Dec 2008) |  | 15 | 20 | 25 | 0.1666666666666667 | 0.2222222222222222 | 0.2777777777777778 |  |  | Should show up only if Monthly option is selected in C3 plus should only calculate volumes for Dec 2008 from worksheet named "Volumes"
Assumptions:
Work Month (in Hours) |  | 150
Utilization % of: |  | 0.6
Revised Work Month (in Hours) |  | 90 |  | Notes:
 |  |  |  | If Annualized Year option = [NAME] to '[NAME] '!B4, '[NAME] '!C4 & '[NAME] '!E4
 |  |  |  | If 1st Quarter  option = [NAME] to '[NAME] '!B9:D9, + '[NAME] '!B14:D14, + '[NAME] '!B19:[NAME]
For Calculation Purposes Only |  |  |  | If 2nd Quarter  option = '[NAME] '!B23:D23, + '[NAME] '!B28:D28, + '[NAME] '!B33:D33
Year | Number of Months |  |  | likewise 3rd and 4th Quarter
Month by Month |  |  |  | If monthly option selected = [NAME] to monthly totals in [NAME] worksheet e.g.,
1st Quarter only |  |  |  | [NAME] to '[NAME] '!B9:D9, + Refer to '[NAME] '!B14:D14, + Refer to '[NAME] '!B19:[NAME] and so on
2nd Quarter only
3rd Quarter only
4th Quarter only
Annualized Year | 12
Month | Numerically | Weeks |  | Numerically | Weeks
0 | 1 | 0 |  | 0 | 0
April | 4 | 17.14285714285714 |  | 1 | 4.428571428571429
August | 8 | 34.71428571428571 |  | 2 | 8.428571428571429
December | 12 | 52.14285714285714 |  | 3 | 12.85714285714286
February | 2 | 8.428571428571429 |  | 4 | 17.14285714285714
January | 1 | 4.428571428571429 |  | 5 | 21.57142857142857
July | 7 | 30.28571428571428 |  | 6 | 25.85714285714285
June | 6 | 25.85714285714285 |  | 7 | 30.28571428571428
March | 3 | 12.85714285714286 |  | 8 | 34.71428571428571
May | 5 | 21.57142857142857 |  | 9 | 39
November | 11 | 47.71428571428571 |  | 10 | 43.42857142857142
October | 10 | 43.42857142857142 |  | 11 | 47.71428571428571
September | 9 | 38.99999999999999 |  | 12 | 52.14285714285714