I have a number of groups of staff, each GROUP has different expectations for the types of work that we do (EVENTS). Within the groups I have staff that work different numbers of days (FTE VALUE). I need a formula that will tell me how many EVENTS are expected for each staff member, based on which GROUP they are in and their FTE VALUE. This will be worked out one row at a time. I have 5 different GROUPS and 3 different FTE VALUES.
Possible combinations each are:
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 6
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 3
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 3
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 5
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 4
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
and so on for each row and each event type
Possible combinations each are:
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 6
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 3
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 3
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "1" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 5
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.8" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "IRR" the expectation amount for Event Type 1 would be 4
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "IQA" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "ICAO" the expectation amount Event Type 1 would be 2
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "QACAO" the expectation amount Event Type 1 would be 1
If cell G3(FTE VALUE) is "0.6" and H3 (GROUP) is "Secondment" the expectation amount Event Type 1 would be 1
and so on for each row and each event type
| Example Mini Sheet.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Monthly Typical Expectations (1 FTE) | Expectations | |||||||||||
| 2 | Groups | Event type 1 | Event type 2 | Event type 3 | Name | FTE Value | Group | Event Type 1 | Event Type 2 | Event Type 3 | |||
| 3 | IRR | 6 | 2 | 2 | Staff 1 | ||||||||
| 4 | IQA | 3 | 4 | 1 | Staff 2 | ||||||||
| 5 | ICAO | 3 | 3 | 1 | Staff 3 | ||||||||
| 6 | QACAO | 2 | 2 | 1 | Staff 4 | ||||||||
| 7 | Secondment | 1 | 0 | 0 | Staff 5 | ||||||||
| 8 | Staff 6 | ||||||||||||
| 9 | Monthly Typical Expectations (0.8 FTE) | Staff 7 | |||||||||||
| 10 | Groups | Event type 1 | Event type 2 | Event type 3 | Staff 8 | ||||||||
| 11 | IRR | 5 | 2 | 2 | Staff 9 | ||||||||
| 12 | IQA | 2 | 3 | 1 | Staff 10 | ||||||||
| 13 | ICAO | 2 | 2 | 1 | Staff 11 | ||||||||
| 14 | QACAO | 2 | 2 | 1 | Staff 12 | ||||||||
| 15 | Secondment | 1 | 0 | 0 | Staff 13 | ||||||||
| 16 | Staff 14 | ||||||||||||
| 17 | Monthly Typical Expectations (0.6 FTE) | Staff 15 | |||||||||||
| 18 | Groups | Event type 1 | Event type 2 | Event type 3 | |||||||||
| 19 | IRR | 4 | 1 | 1 | |||||||||
| 20 | IQA | 2 | 2 | 1 | |||||||||
| 21 | ICAO | 2 | 2 | 1 | |||||||||
| 22 | QACAO | 1 | 1 | 1 | |||||||||
| 23 | Secondment | 1 | 0 | 0 | |||||||||
Sheet1 | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B11:D14 | B11 | =B3*0.8 |
| B19:D22 | B19 | =B3*0.6 |
