I have a formula that almost does everything I want but I have two issues I can't figure out how to add in.
I'm trying to calculate missed overtime to charge against people not available to work for each day, in one cell. I have three columns labeled "OT W" (overtime worked), "U" (unavailable), and "Key" (drop down list of reasons unavailable). Depending on the reason someone is unavailable, I need multiple criteria to calculate different things when a reason is selected under "Key" and populate an answer to go in the cell under "U" for each day.
There are two overtime lists, 10 hours and 12 hours. This is indicated under "LIST" above each person's name. At the end of each quarter, all overtime people need to be within 10% of the top worker. Because some can sign up for the 10 hour list, it limits them being equal or close to those that work 12 hours, so in the formula I have, when "10LIST" is selected under "Key", it will calculate overtime over 2 hours than subtract what they worked to give the difference, and populate an average of unavailable under the "U".
In the worksheet, row 17, on Saturday 4/1, I have 2 12-hr people that worked, 2.51 hrs and 2.47 hrs, average of 2.49. There are 2 10-hr people that worked, 1.94 hrs and 1.50 hrs. The 2 10-hr people need to be charged unavailable to bring them close to the 12-hr people average of 2.49, so the formula I have, when "10LIST" is selected under "Key", it gives them 0.55 and 0.99 under the "U" column to bring their total to 2.49. That part works.
First issue is when the 10-hr people work more than 2 hours of overtime, the average is skewed as seen in row 19, on Monday 4/3. I have 2 12-hr people that worked 3.17 and 3.36, average of 3.27. I have 3 10-hr people that all worked over 2 hours, 2.69, 2.57, and 2.37. To bring the them to the average, they should be charged .58, .70, and .90. This is where I can't figure out how to only calculate what the 12-hr people work over 2 hours instead of all of them (and the reason for "all of them is below").
The second issue is when someone is on vacation leave for a day, schedule change, refuses the overtime, or does it down time, and that both lists work overtime, I want to be able to average all the overtime worked that day, divided by the number of people that worked. (If they didn't work overtime, I don't want them included in the average). The formula I have will do this but only for the "OT W" (the overtime worked). I can't figure out how to combine the 10-hr people to include in their total the "U" (unavailable being charged) to them to make the total accurate accurate. As you can see on either 4/1 or 4/3, it takes all of the times listed under "OT W", but not "U" so it creates a different average. So, I need the formula to first add the 10-hr people's "OT W" and "U" (if any), then take that answer and add what the 12-hr people worked and then average it all by the number of people that worked and populate the answer into the people who's "Key" is either LV, SC, REF, or OTDT under "U". If the 10-hr people aren't being charged unavailable, then the "U" doesn't need to be added, it's only if their "Key" shows "10LIST". Phew! is it too much for one cell?
I'm trying to calculate missed overtime to charge against people not available to work for each day, in one cell. I have three columns labeled "OT W" (overtime worked), "U" (unavailable), and "Key" (drop down list of reasons unavailable). Depending on the reason someone is unavailable, I need multiple criteria to calculate different things when a reason is selected under "Key" and populate an answer to go in the cell under "U" for each day.
There are two overtime lists, 10 hours and 12 hours. This is indicated under "LIST" above each person's name. At the end of each quarter, all overtime people need to be within 10% of the top worker. Because some can sign up for the 10 hour list, it limits them being equal or close to those that work 12 hours, so in the formula I have, when "10LIST" is selected under "Key", it will calculate overtime over 2 hours than subtract what they worked to give the difference, and populate an average of unavailable under the "U".
In the worksheet, row 17, on Saturday 4/1, I have 2 12-hr people that worked, 2.51 hrs and 2.47 hrs, average of 2.49. There are 2 10-hr people that worked, 1.94 hrs and 1.50 hrs. The 2 10-hr people need to be charged unavailable to bring them close to the 12-hr people average of 2.49, so the formula I have, when "10LIST" is selected under "Key", it gives them 0.55 and 0.99 under the "U" column to bring their total to 2.49. That part works.
First issue is when the 10-hr people work more than 2 hours of overtime, the average is skewed as seen in row 19, on Monday 4/3. I have 2 12-hr people that worked 3.17 and 3.36, average of 3.27. I have 3 10-hr people that all worked over 2 hours, 2.69, 2.57, and 2.37. To bring the them to the average, they should be charged .58, .70, and .90. This is where I can't figure out how to only calculate what the 12-hr people work over 2 hours instead of all of them (and the reason for "all of them is below").
The second issue is when someone is on vacation leave for a day, schedule change, refuses the overtime, or does it down time, and that both lists work overtime, I want to be able to average all the overtime worked that day, divided by the number of people that worked. (If they didn't work overtime, I don't want them included in the average). The formula I have will do this but only for the "OT W" (the overtime worked). I can't figure out how to combine the 10-hr people to include in their total the "U" (unavailable being charged) to them to make the total accurate accurate. As you can see on either 4/1 or 4/3, it takes all of the times listed under "OT W", but not "U" so it creates a different average. So, I need the formula to first add the 10-hr people's "OT W" and "U" (if any), then take that answer and add what the 12-hr people worked and then average it all by the number of people that worked and populate the answer into the people who's "Key" is either LV, SC, REF, or OTDT under "U". If the 10-hr people aren't being charged unavailable, then the "U" doesn't need to be added, it's only if their "Key" shows "10LIST". Phew! is it too much for one cell?
| 49008-OT Quarter 2-2023.xlsb | |||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | |||
| 13 | G - | GREEN | Rte | LIST | NS DAY | TO | TOTW | TU | TA | Rte | LIST | NS DAY | TO | TOTW | TU | TA | Rte | LIST | NS DAY | TO | TOTW | TU | TA | Rte | LIST | NS DAY | TO | TOTW | TU | TA | Rte | LIST | NS DAY | TO | TOTW | TU | TA | Rte | LIST | NS DAY | TO | TOTW | TU | TA | |||
| 14 | W - | BROWN | 846 | 12 | Blue | 2 | 13.68 | 0.00 | 13.68 | T6-08-2 | 10 | Black | 1 | 2.69 | 2.25 | 4.94 | 859 | 10 | Black | 2 | 4.51 | 0.81 | 5.32 | 866 | 10 | Red | 2 | 3.87 | 1.45 | 5.32 | 838 | 12 | Red | 0 | 0.00 | 4.94 | 4.94 | 867 | 12 | Black | 2 | 5.83 | 0.00 | 5.83 | |||
| 15 | R - | RED | WEEK | 1 | Soltesz | 5 | Sienicki | 3 | Weir | 4 | Bolen | 6 | Dailey | 2 | Galloway | ||||||||||||||||||||||||||||||||
| 16 | K - | BLACK | 1 | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | SDO | OT W | U | Key | O | DT | TA | ||
| 17 | SAT | 4/1 | Y | 2.51 | 0.00 | 1 | 2.51 | 2.51 | 2.11 | LV | 0 | 2.11 | 2.11 | 1.94 | 0.55 | 10LIST | 1 | 2.49 | 2.49 | 1.50 | 0.99 | 10LIST | 1 | 2.49 | 2.49 | 2.11 | LV | 0 | 2.11 | 2.11 | 2.47 | 0.00 | 1 | 2.47 | 2.47 | ||||||||||||
| 18 | SUN | 4/2 | Y | 0.00 | 0 | 0.00 | 2.51 | 0.00 | 0 | 0.00 | 2.11 | 0.00 | 0 | 0.00 | 2.49 | 0.00 | 0 | 0.00 | 2.49 | 0.00 | 0 | 0.00 | 2.11 | 0.00 | 0 | 0.00 | 2.47 | ||||||||||||||||||||
| 19 | MON | 4/3 | B | 8.00 | 3.17 | 0.00 | 1 | 11.17 | 13.68 | 2.69 | 0.14 | 10LIST | 1 | 2.83 | 4.94 | 2.57 | 0.26 | 10LIST | 1 | 2.83 | 5.32 | 2.37 | 0.46 | 10LIST | 1 | 2.83 | 5.32 | 2.83 | LV | 0 | 2.83 | 4.94 | 3.36 | 0.00 | 1 | 3.36 | 5.83 | ||||||||||
| 20 | TUE | 4/4 | G | 0.00 | 0 | 0.00 | 13.68 | 0.00 | 0 | 0.00 | 4.94 | 0.00 | 0 | 0.00 | 5.32 | 0.00 | 0 | 0.00 | 5.32 | 0.00 | 0 | 0.00 | 4.94 | 0.00 | 0 | 0.00 | 5.83 | ||||||||||||||||||||
Quarter | |||||||||||||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F14 | F14 | =IF(E14=Schedule!A2,Schedule!D2,IF(E14=Schedule!A3,Schedule!D3,IF(E14=Schedule!A4,Schedule!D4,IF(E14=Schedule!A5,Schedule!D5,IF(E14=Schedule!A6,Schedule!D6,IF(E14=Schedule!A7,Schedule!D7,IF(E14=Schedule!A8,Schedule!D8,IF(E14=Schedule!A9,Schedule!D9,IF(E14=Schedule!A10,Schedule!D10,IF(E14=Schedule!A11,Schedule!D11,IF(E14=Schedule!A12,Schedule!D12,IF(E14=Schedule!A13,Schedule!D13,IF(E14=Schedule!A14,Schedule!D14,IF(E14=Schedule!A15,Schedule!D15,IF(E14=Schedule!A16,Schedule!D16,IF(E14=Schedule!A17,Schedule!D17,IF(E14=Schedule!A18,Schedule!D18,IF(E14=Schedule!A19,Schedule!D19,IF(E14=Schedule!A20,Schedule!D20,IF(E14=Schedule!A21,Schedule!D21,IF(E14=Schedule!A22,Schedule!D22,IF(E14=Schedule!A23,Schedule!D23,IF(E14=Schedule!A24,Schedule!D24,IF(E14=Schedule!A25,Schedule!D25)))))))))))))))))))))))) |
| G14 | G14 | =IF(E14=Schedule!A2,Schedule!B2,IF(E14=Schedule!A3,Schedule!B3,IF(E14=Schedule!A4,Schedule!B4,IF(E14=Schedule!A5,Schedule!B5,IF(E14=Schedule!A6,Schedule!B6,IF(E14=Schedule!A7,Schedule!B7,IF(E14=Schedule!A8,Schedule!B8,IF(E14=Schedule!A9,Schedule!B9,IF(E14=Schedule!A10,Schedule!B10,IF(E14=Schedule!A11,Schedule!B11,IF(E14=Schedule!A12,Schedule!B12,IF(E14=Schedule!A13,Schedule!B13,IF(E14=Schedule!A14,Schedule!B14,IF(E14=Schedule!A15,Schedule!B15,IF(E14=Schedule!A16,Schedule!B16,IF(E14=Schedule!A17,Schedule!B17,IF(E14=Schedule!A18,Schedule!B18,IF(E14=Schedule!A19,Schedule!B19,IF(E14=Schedule!A20,Schedule!B20,IF(E14=Schedule!A21,Schedule!B21,IF(E14=Schedule!A22,Schedule!B22,IF(E14=Schedule!A23,Schedule!B23,IF(E14=Schedule!A24,Schedule!B24,IF(E14=Schedule!A25,Schedule!B25)))))))))))))))))))))))) |
| H14,AQ14,AJ14,AC14,V14,O14 | H14 | =IF($D$16=1,SUMIF(I17:I23,"<>"),IF($D$16=2,SUMIF(I17:I30,"<>"),IF($D$16=3,SUMIF(I17:I37,"<>"),IF($D$16=4,SUMIF(I17:I44,"<>"),IF($D$16=5,SUMIF(I17:I51,"<>"),IF($D$16=6,SUMIF(I17:I58,"<>"),IF($D$16=7,SUMIF(I17:I265,"<>"),IF($D$16=8,SUMIF(I17:I72,"<>"),IF($D$16=9,SUMIF(I17:I79,"<>"),IF($D$16=10,SUMIF(I17:I86,"<>"),IF($D$16=11,SUMIF(I17:I93,"<>"),IF($D$16=12,SUMIF(I17:I100,"<>"),IF($D$16=13,SUMIF(I17:I107,"<>"),IF($D$16=14,SUMIF(I17:I114,"<>"),)))))))))))))) |
| I14,AR14,AK14,AD14,W14,P14 | I14 | =IF($D$16=1,SUMIF(E17:F23,"<>"),IF($D$16=2,SUMIF(E17:F30,"<>"),IF($D$16=3,SUMIF(E17:F37,"<>"),IF($D$16=4,SUMIF(E17:F44,"<>"),IF($D$16=5,SUMIF(E17:F51,"<>"),IF($D$16=6,SUMIF(E17:F58,"<>"),IF($D$16=7,SUMIF(E17:F265,"<>"),IF($D$16=8,SUMIF(E17:F72,"<>"),IF($D$16=9,SUMIF(E17:F79,"<>"),IF($D$16=10,SUMIF(E17:F86,"<>"),IF($D$16=11,SUMIF(E17:F93,"<>"),IF($D$16=12,SUMIF(E17:F100,"<>"),IF($D$16=13,SUMIF(E17:F107,"<>"),IF($D$16=14,SUMIF(E17:F114,"<>"),)))))))))))))) |
| J14,AS14,AL14,AE14,X14,Q14 | J14 | =IF($D$16=1,SUMIF(G17:G23,"<>"),IF($D$16=2,SUMIF(G17:G30,"<>"),IF($D$16=3,SUMIF(G17:G37,"<>"),IF($D$16=4,SUMIF(G17:G44,"<>"),IF($D$16=5,SUMIF(G17:G51,"<>"),IF($D$16=6,SUMIF(G17:G58,"<>"),IF($D$16=7,SUMIF(G17:G265,"<>"),IF($D$16=8,SUMIF(G17:G72,"<>"),IF($D$16=9,SUMIF(G17:G79,"<>"),IF($D$16=10,SUMIF(G17:G86,"<>"),IF($D$16=11,SUMIF(G17:G93,"<>"),IF($D$16=12,SUMIF(G17:G100,"<>"),IF($D$16=13,SUMIF(G17:G107,"<>"),IF($D$16=14,SUMIF(G17:G114,"<>"),)))))))))))))) |
| K14,AT14,AM14,AF14,Y14,R14 | K14 | =IF($D$16=1,K23,IF($D$16=2,K30,IF($D$16=3,K37,IF($D$16=4,K44,IF($D$16=5,K51,IF($D$16=6,K58,IF($D$16=7,K65,IF($D$16=8,K72,IF($D$16=9,K79,IF($D$16=10,K86,IF($D$16=11,K93,IF($D$16=12,K100,IF($D$16=13,K107,IF($D$16=14,K114,)))))))))))))) |
| M14 | M14 | =IF(L14=Schedule!A2,Schedule!D2,IF(L14=Schedule!A3,Schedule!D3,IF(L14=Schedule!A4,Schedule!D4,IF(L14=Schedule!A5,Schedule!D5,IF(L14=Schedule!A6,Schedule!D6,IF(L14=Schedule!A7,Schedule!D7,IF(L14=Schedule!A8,Schedule!D8,IF(L14=Schedule!A9,Schedule!D9,IF(L14=Schedule!A10,Schedule!D10,IF(L14=Schedule!A11,Schedule!D11,IF(L14=Schedule!A12,Schedule!D12,IF(L14=Schedule!A13,Schedule!D13,IF(L14=Schedule!A14,Schedule!D14,IF(L14=Schedule!A15,Schedule!D15,IF(L14=Schedule!A16,Schedule!D16,IF(L14=Schedule!A17,Schedule!D17,IF(L14=Schedule!A18,Schedule!D18,IF(L14=Schedule!A19,Schedule!D19,IF(L14=Schedule!A20,Schedule!D20,IF(L14=Schedule!A21,Schedule!D21,IF(L14=Schedule!A22,Schedule!D22,IF(L14=Schedule!A23,Schedule!D23,IF(L14=Schedule!A24,Schedule!D24,IF(L14=Schedule!A25,Schedule!D25)))))))))))))))))))))))) |
| N14 | N14 | =IF(L14=Schedule!A2,Schedule!B2,IF(L14=Schedule!A3,Schedule!B3,IF(L14=Schedule!A4,Schedule!B4,IF(L14=Schedule!A5,Schedule!B5,IF(L14=Schedule!A6,Schedule!B6,IF(L14=Schedule!A7,Schedule!B7,IF(L14=Schedule!A8,Schedule!B8,IF(L14=Schedule!A9,Schedule!B9,IF(L14=Schedule!A10,Schedule!B10,IF(L14=Schedule!A11,Schedule!B11,IF(L14=Schedule!A12,Schedule!B12,IF(L14=Schedule!A13,Schedule!B13,IF(L14=Schedule!A14,Schedule!B14,IF(L14=Schedule!A15,Schedule!B15,IF(L14=Schedule!A16,Schedule!B16,IF(L14=Schedule!A17,Schedule!B17,IF(L14=Schedule!A18,Schedule!B18,IF(L14=Schedule!A19,Schedule!B19,IF(L14=Schedule!A20,Schedule!B20,IF(L14=Schedule!A21,Schedule!B21,IF(L14=Schedule!A22,Schedule!B22,IF(L14=Schedule!A23,Schedule!B23,IF(L14=Schedule!A24,Schedule!B24,IF(L14=Schedule!A25,Schedule!B25)))))))))))))))))))))))) |
| T14 | T14 | =IF(S14=Schedule!A2,Schedule!D2,IF(S14=Schedule!A3,Schedule!D3,IF(S14=Schedule!A4,Schedule!D4,IF(S14=Schedule!A5,Schedule!D5,IF(S14=Schedule!A6,Schedule!D6,IF(S14=Schedule!A7,Schedule!D7,IF(S14=Schedule!A8,Schedule!D8,IF(S14=Schedule!A9,Schedule!D9,IF(S14=Schedule!A10,Schedule!D10,IF(S14=Schedule!A11,Schedule!D11,IF(S14=Schedule!A12,Schedule!D12,IF(S14=Schedule!A13,Schedule!D13,IF(S14=Schedule!A14,Schedule!D14,IF(S14=Schedule!A15,Schedule!D15,IF(S14=Schedule!A16,Schedule!D16,IF(S14=Schedule!A17,Schedule!D17,IF(S14=Schedule!A18,Schedule!D18,IF(S14=Schedule!A19,Schedule!D19,IF(S14=Schedule!A20,Schedule!D20,IF(S14=Schedule!A21,Schedule!D21,IF(S14=Schedule!A22,Schedule!D22,IF(S14=Schedule!A23,Schedule!D23,IF(S14=Schedule!A24,Schedule!D24,IF(S14=Schedule!A25,Schedule!D25)))))))))))))))))))))))) |
| U14 | U14 | =IF(S14=Schedule!A2,Schedule!B2,IF(S14=Schedule!A3,Schedule!B3,IF(S14=Schedule!A4,Schedule!B4,IF(S14=Schedule!A5,Schedule!B5,IF(S14=Schedule!A6,Schedule!B6,IF(S14=Schedule!A7,Schedule!B7,IF(S14=Schedule!A8,Schedule!B8,IF(S14=Schedule!A9,Schedule!B9,IF(S14=Schedule!A10,Schedule!B10,IF(S14=Schedule!A11,Schedule!B11,IF(S14=Schedule!A12,Schedule!B12,IF(S14=Schedule!A13,Schedule!B13,IF(S14=Schedule!A14,Schedule!B14,IF(S14=Schedule!A15,Schedule!B15,IF(S14=Schedule!A16,Schedule!B16,IF(S14=Schedule!A17,Schedule!B17,IF(S14=Schedule!A18,Schedule!B18,IF(S14=Schedule!A19,Schedule!B19,IF(S14=Schedule!A20,Schedule!B20,IF(S14=Schedule!A21,Schedule!B21,IF(S14=Schedule!A22,Schedule!B22,IF(S14=Schedule!A23,Schedule!B23,IF(S14=Schedule!A24,Schedule!B24,IF(S14=Schedule!A25,Schedule!B25)))))))))))))))))))))))) |
| AA14 | AA14 | =IF(Z14=Schedule!A2,Schedule!D2,IF(Z14=Schedule!A3,Schedule!D3,IF(Z14=Schedule!A4,Schedule!D4,IF(Z14=Schedule!A5,Schedule!D5,IF(Z14=Schedule!A6,Schedule!D6,IF(Z14=Schedule!A7,Schedule!D7,IF(Z14=Schedule!A8,Schedule!D8,IF(Z14=Schedule!A9,Schedule!D9,IF(Z14=Schedule!A10,Schedule!D10,IF(Z14=Schedule!A11,Schedule!D11,IF(Z14=Schedule!A12,Schedule!D12,IF(Z14=Schedule!A13,Schedule!D13,IF(Z14=Schedule!A14,Schedule!D14,IF(Z14=Schedule!A15,Schedule!D15,IF(Z14=Schedule!A16,Schedule!D16,IF(Z14=Schedule!A17,Schedule!D17,IF(Z14=Schedule!A18,Schedule!D18,IF(Z14=Schedule!A19,Schedule!D19,IF(Z14=Schedule!A20,Schedule!D20,IF(Z14=Schedule!A21,Schedule!D21,IF(Z14=Schedule!A22,Schedule!D22,IF(Z14=Schedule!A23,Schedule!D23,IF(Z14=Schedule!A24,Schedule!D24,IF(Z14=Schedule!A25,Schedule!D25)))))))))))))))))))))))) |
| AB14 | AB14 | =IF(Z14=Schedule!A2,Schedule!B2,IF(Z14=Schedule!A3,Schedule!B3,IF(Z14=Schedule!A4,Schedule!B4,IF(Z14=Schedule!A5,Schedule!B5,IF(Z14=Schedule!A6,Schedule!B6,IF(Z14=Schedule!A7,Schedule!B7,IF(Z14=Schedule!A8,Schedule!B8,IF(Z14=Schedule!A9,Schedule!B9,IF(Z14=Schedule!A10,Schedule!B10,IF(Z14=Schedule!A11,Schedule!B11,IF(Z14=Schedule!A12,Schedule!B12,IF(Z14=Schedule!A13,Schedule!B13,IF(Z14=Schedule!A14,Schedule!B14,IF(Z14=Schedule!A15,Schedule!B15,IF(Z14=Schedule!A16,Schedule!B16,IF(Z14=Schedule!A17,Schedule!B17,IF(Z14=Schedule!A18,Schedule!B18,IF(Z14=Schedule!A19,Schedule!B19,IF(Z14=Schedule!A20,Schedule!B20,IF(Z14=Schedule!A21,Schedule!B21,IF(Z14=Schedule!A22,Schedule!B22,IF(Z14=Schedule!A23,Schedule!B23,IF(Z14=Schedule!A24,Schedule!B24,IF(Z14=Schedule!A25,Schedule!B25)))))))))))))))))))))))) |
| AH14 | AH14 | =IF(AG14=Schedule!A2,Schedule!D2,IF(AG14=Schedule!A3,Schedule!D3,IF(AG14=Schedule!A4,Schedule!D4,IF(AG14=Schedule!A5,Schedule!D5,IF(AG14=Schedule!A6,Schedule!D6,IF(AG14=Schedule!A7,Schedule!D7,IF(AG14=Schedule!A8,Schedule!D8,IF(AG14=Schedule!A9,Schedule!D9,IF(AG14=Schedule!A10,Schedule!D10,IF(AG14=Schedule!A11,Schedule!D11,IF(AG14=Schedule!A12,Schedule!D12,IF(AG14=Schedule!A13,Schedule!D13,IF(AG14=Schedule!A14,Schedule!D14,IF(AG14=Schedule!A15,Schedule!D15,IF(AG14=Schedule!A16,Schedule!D16,IF(AG14=Schedule!A17,Schedule!D17,IF(AG14=Schedule!A18,Schedule!D18,IF(AG14=Schedule!A19,Schedule!D19,IF(AG14=Schedule!A20,Schedule!D20,IF(AG14=Schedule!A21,Schedule!D21,IF(AG14=Schedule!A22,Schedule!D22,IF(AG14=Schedule!A23,Schedule!D23,IF(AG14=Schedule!A24,Schedule!D24,IF(AG14=Schedule!A25,Schedule!D25)))))))))))))))))))))))) |
| AI14 | AI14 | =IF(AG14=Schedule!A2,Schedule!B2,IF(AG14=Schedule!A3,Schedule!B3,IF(AG14=Schedule!A4,Schedule!B4,IF(AG14=Schedule!A5,Schedule!B5,IF(AG14=Schedule!A6,Schedule!B6,IF(AG14=Schedule!A7,Schedule!B7,IF(AG14=Schedule!A8,Schedule!B8,IF(AG14=Schedule!A9,Schedule!B9,IF(AG14=Schedule!A10,Schedule!B10,IF(AG14=Schedule!A11,Schedule!B11,IF(AG14=Schedule!A12,Schedule!B12,IF(AG14=Schedule!A13,Schedule!B13,IF(AG14=Schedule!A14,Schedule!B14,IF(AG14=Schedule!A15,Schedule!B15,IF(AG14=Schedule!A16,Schedule!B16,IF(AG14=Schedule!A17,Schedule!B17,IF(AG14=Schedule!A18,Schedule!B18,IF(AG14=Schedule!A19,Schedule!B19,IF(AG14=Schedule!A20,Schedule!AHX20,IF(AG14=Schedule!A21,Schedule!B21,IF(AG14=Schedule!A22,Schedule!B22,IF(AG14=Schedule!A23,Schedule!B23,IF(AG14=Schedule!A24,Schedule!B24,IF(AG14=Schedule!A25,Schedule!B25)))))))))))))))))))))))) |
| AO14 | AO14 | =IF(AN14=Schedule!A2,Schedule!D2,IF(AN14=Schedule!A3,Schedule!D3,IF(AN14=Schedule!A4,Schedule!D4,IF(AN14=Schedule!A5,Schedule!D5,IF(AN14=Schedule!A6,Schedule!D6,IF(AN14=Schedule!A7,Schedule!D7,IF(AN14=Schedule!A8,Schedule!D8,IF(AN14=Schedule!A9,Schedule!D9,IF(AN14=Schedule!A10,Schedule!D10,IF(AN14=Schedule!A11,Schedule!D11,IF(AN14=Schedule!A12,Schedule!D12,IF(AN14=Schedule!A13,Schedule!D13,IF(AN14=Schedule!A14,Schedule!D14,IF(AN14=Schedule!A15,Schedule!D15,IF(AN14=Schedule!A16,Schedule!D16,IF(AN14=Schedule!A17,Schedule!D17,IF(AN14=Schedule!A18,Schedule!D18,IF(AN14=Schedule!A19,Schedule!D19,IF(AN14=Schedule!A20,Schedule!D20,IF(AN14=Schedule!A21,Schedule!D21,IF(AN14=Schedule!A22,Schedule!D22,IF(AN14=Schedule!A23,Schedule!D23,IF(AN14=Schedule!A24,Schedule!D24,IF(AN14=Schedule!A25,Schedule!D25)))))))))))))))))))))))) |
| AP14 | AP14 | =IF(AN14=Schedule!A2,Schedule!B2,IF(AN14=Schedule!A3,Schedule!B3,IF(AN14=Schedule!A4,Schedule!B4,IF(AN14=Schedule!A5,Schedule!B5,IF(AN14=Schedule!A6,Schedule!B6,IF(AN14=Schedule!A7,Schedule!B7,IF(AN14=Schedule!A8,Schedule!B8,IF(AN14=Schedule!A9,Schedule!B9,IF(AN14=Schedule!A10,Schedule!B10,IF(AN14=Schedule!A11,Schedule!B11,IF(AN14=Schedule!A12,Schedule!B12,IF(AN14=Schedule!A13,Schedule!B13,IF(AN14=Schedule!A14,Schedule!B14,IF(AN14=Schedule!A15,Schedule!B15,IF(AN14=Schedule!A16,Schedule!B16,IF(AN14=Schedule!A17,Schedule!B17,IF(AN14=Schedule!A18,Schedule!B18,IF(AN14=Schedule!A19,Schedule!B19,IF(AN14=Schedule!A20,Schedule!B20,IF(AN14=Schedule!A21,Schedule!B21,IF(AN14=Schedule!A22,Schedule!B22,IF(AN14=Schedule!A23,Schedule!B23,IF(AN14=Schedule!A24,Schedule!B24,IF(AN14=Schedule!A25,Schedule!B25)))))))))))))))))))))))) |
| E15,L15,S15,Z15,AG15,AN15 | E15 | =RANK.EQ(K14,($K$14,$R$14,$Y$14,$AF$14,$AM$14,$AT$14,$BA$14,$BH$14,$BO$14,$BV$14,$CC$14,$CJ$14,$CQ$14,$CX$14,$DE$14,$DL$14,$DS$14,$DZ$14,$EG$14,$EN$14,$EU$14,$FB$14,$FI$14))+COUNTIFS($K$14:K14,K14,$K$13:K13,"TA")-1 |
| F15 | F15 | =IF(E14=Schedule!A2,Schedule!C2,IF(E14=Schedule!A3,Schedule!C3,IF(E14=Schedule!A4,Schedule!C4,IF(E14=Schedule!A5,Schedule!C5,IF(E14=Schedule!A6,Schedule!C6,IF(E14=Schedule!A7,Schedule!C7,IF(E14=Schedule!A8,Schedule!C8,IF(E14=Schedule!A9,Schedule!C9,IF(E14=Schedule!A10,Schedule!C10,IF(E14=Schedule!A11,Schedule!C11,IF(E14=Schedule!A12,Schedule!C12,IF(E14=Schedule!A13,Schedule!C13,IF(E14=Schedule!A14,Schedule!C14,IF(E14=Schedule!A15,Schedule!C15,IF(E14=Schedule!A16,Schedule!C16,IF(E14=Schedule!A17,Schedule!C17,IF(E14=Schedule!A18,Schedule!C18,IF(E14=Schedule!A19,Schedule!C19,IF(E14=Schedule!A20,Schedule!C20,IF(E14=Schedule!A21,Schedule!C21,IF(E14=Schedule!A22,Schedule!C22,IF(E14=Schedule!A23,Schedule!C23,IF(E14=Schedule!A24,Schedule!C24,IF(E14=Schedule!A25,Schedule!C25)))))))))))))))))))))))) |
| M15 | M15 | =IF(L14=Schedule!A2,Schedule!C2,IF(L14=Schedule!A3,Schedule!C3,IF(L14=Schedule!A4,Schedule!C4,IF(L14=Schedule!A5,Schedule!C5,IF(L14=Schedule!A6,Schedule!C6,IF(L14=Schedule!A7,Schedule!C7,IF(L14=Schedule!A8,Schedule!C8,IF(L14=Schedule!A9,Schedule!C9,IF(L14=Schedule!A10,Schedule!C10,IF(L14=Schedule!A11,Schedule!C11,IF(L14=Schedule!A12,Schedule!C12,IF(L14=Schedule!A13,Schedule!C13,IF(L14=Schedule!A14,Schedule!C14,IF(L14=Schedule!A15,Schedule!C15,IF(L14=Schedule!A16,Schedule!C16,IF(L14=Schedule!A17,Schedule!C17,IF(L14=Schedule!A18,Schedule!C18,IF(L14=Schedule!A19,Schedule!C19,IF(L14=Schedule!A20,Schedule!C20,IF(L14=Schedule!A21,Schedule!C21,IF(L14=Schedule!A22,Schedule!C22,IF(L14=Schedule!A23,Schedule!C23,IF(L14=Schedule!A24,Schedule!C24,IF(L14=Schedule!A25,Schedule!C25)))))))))))))))))))))))) |
| T15 | T15 | =IF(S14=Schedule!A2,Schedule!C2,IF(S14=Schedule!A3,Schedule!C3,IF(S14=Schedule!A4,Schedule!C4,IF(S14=Schedule!A5,Schedule!C5,IF(S14=Schedule!A6,Schedule!C6,IF(S14=Schedule!A7,Schedule!C7,IF(S14=Schedule!A8,Schedule!C8,IF(S14=Schedule!A9,Schedule!C9,IF(S14=Schedule!A10,Schedule!C10,IF(S14=Schedule!A11,Schedule!C11,IF(S14=Schedule!A12,Schedule!C12,IF(S14=Schedule!A13,Schedule!C13,IF(S14=Schedule!A14,Schedule!C14,IF(S14=Schedule!A15,Schedule!C15,IF(S14=Schedule!A16,Schedule!C16,IF(S14=Schedule!A17,Schedule!C17,IF(S14=Schedule!A18,Schedule!C18,IF(S14=Schedule!A19,Schedule!C19,IF(S14=Schedule!A20,Schedule!C20,IF(S14=Schedule!A21,Schedule!C21,IF(S14=Schedule!A22,Schedule!C22,IF(S14=Schedule!A23,Schedule!C23,IF(S14=Schedule!A24,Schedule!C24,IF(S14=Schedule!A25,Schedule!C25)))))))))))))))))))))))) |
| AA15 | AA15 | =IF(Z14=Schedule!A2,Schedule!C2,IF(Z14=Schedule!A3,Schedule!C3,IF(Z14=Schedule!A4,Schedule!C4,IF(Z14=Schedule!A5,Schedule!C5,IF(Z14=Schedule!A6,Schedule!C6,IF(Z14=Schedule!A7,Schedule!C7,IF(Z14=Schedule!A8,Schedule!C8,IF(Z14=Schedule!A9,Schedule!C9,IF(Z14=Schedule!A10,Schedule!C10,IF(Z14=Schedule!A11,Schedule!C11,IF(Z14=Schedule!A12,Schedule!C12,IF(Z14=Schedule!A13,Schedule!C13,IF(Z14=Schedule!A14,Schedule!C14,IF(Z14=Schedule!A15,Schedule!C15,IF(Z14=Schedule!A16,Schedule!C16,IF(Z14=Schedule!A17,Schedule!C17,IF(Z14=Schedule!A18,Schedule!C18,IF(Z14=Schedule!A19,Schedule!C19,IF(Z14=Schedule!A20,Schedule!C20,IF(Z14=Schedule!A21,Schedule!C21,IF(Z14=Schedule!A22,Schedule!C22,IF(Z14=Schedule!A23,Schedule!C23,IF(Z14=Schedule!A24,Schedule!C24,IF(Z14=Schedule!A25,Schedule!C25)))))))))))))))))))))))) |
| AH15 | AH15 | =IF(AG14=Schedule!A2,Schedule!C2,IF(AG14=Schedule!A3,Schedule!C3,IF(AG14=Schedule!A4,Schedule!C4,IF(AG14=Schedule!A5,Schedule!C5,IF(AG14=Schedule!A6,Schedule!C6,IF(AG14=Schedule!A7,Schedule!C7,IF(AG14=Schedule!A8,Schedule!C8,IF(AG14=Schedule!A9,Schedule!C9,IF(AG14=Schedule!A10,Schedule!C10,IF(AG14=Schedule!A11,Schedule!C11,IF(AG14=Schedule!A12,Schedule!C12,IF(AG14=Schedule!A13,Schedule!C13,IF(AG14=Schedule!A14,Schedule!C14,IF(AG14=Schedule!A15,Schedule!C15,IF(AG14=Schedule!A16,Schedule!C16,IF(AG14=Schedule!A17,Schedule!C17,IF(AG14=Schedule!A18,Schedule!C18,IF(AG14=Schedule!A19,Schedule!C19,IF(AG14=Schedule!A20,Schedule!CHX20,IF(AG14=Schedule!A21,Schedule!C21,IF(AG14=Schedule!A22,Schedule!C22,IF(AG14=Schedule!A23,Schedule!C23,IF(AG14=Schedule!A24,Schedule!C24,IF(AG14=Schedule!A25,Schedule!C25)))))))))))))))))))))))) |
| AO15 | AO15 | =IF(AN14=Schedule!A2,Schedule!C2,IF(AN14=Schedule!A3,Schedule!C3,IF(AN14=Schedule!A4,Schedule!C4,IF(AN14=Schedule!A5,Schedule!C5,IF(AN14=Schedule!A6,Schedule!C6,IF(AN14=Schedule!A7,Schedule!C7,IF(AN14=Schedule!A8,Schedule!C8,IF(AN14=Schedule!A9,Schedule!C9,IF(AN14=Schedule!A10,Schedule!C10,IF(AN14=Schedule!A11,Schedule!C11,IF(AN14=Schedule!A12,Schedule!C12,IF(AN14=Schedule!A13,Schedule!C13,IF(AN14=Schedule!A14,Schedule!C14,IF(AN14=Schedule!A15,Schedule!C15,IF(AN14=Schedule!A16,Schedule!C16,IF(AN14=Schedule!A17,Schedule!C17,IF(AN14=Schedule!A18,Schedule!C18,IF(AN14=Schedule!A19,Schedule!C19,IF(AN14=Schedule!A20,Schedule!C20,IF(AN14=Schedule!A21,Schedule!C21,IF(AN14=Schedule!A22,Schedule!C22,IF(AN14=Schedule!A23,Schedule!C23,IF(AN14=Schedule!A24,Schedule!C24,IF(AN14=Schedule!A25,Schedule!C25)))))))))))))))))))))))) |
| I17:I20,AR17:AR20,AK17:AK20,AD17:AD20,W17:W20,P17:P20 | I17 | =VALUE(IF(COUNTIF(E17:F17,">0"),"1",)) |
| J17:J20,AS17:AS20,AL17:AL20,AE17:AE20,X17:X20,Q17:Q20 | J17 | =E17+F17+G17 |
| K17,AT17,AM17,AF17,Y17,R17 | K17 | =J17 |
| K18:K20,AT18:AT20,AM18:AM20,AF18:AF20,Y18:Y20,R18:R20 | K18 | =K17+J18 |
| C18:C20 | C18 | =C17+1 |
| D18 | D18 | =D17 |
| D19:D20 | D19 | =IF(D18="B","G",IF(D18="G","W",IF(D18="W","R",IF(D18="R","K",IF(D18="K","Y","B"))))) |
| G17:G20 | G17 | =IF(H17="",0,IF(H17="RDO",8,IF(H17="10LIST",(AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">2.00")-$F17),IF(OR(H17={"LV","SC","REF","OTDT"}),AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">0.17"),0)))) |
| N17:N20 | N17 | =IF(O17="",0,IF(O17="RDO",8,IF(O17="10LIST",(AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">2.00")-$M17),IF(OR(O17={"LV","SC","REF","OTDT"}),AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">0.17"),0)))) |
| U17:U20 | U17 | =IF(V17="",0,IF(V17="RDO",8,IF(V17="10LIST",(AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">2.00")-$T17),IF(OR(V17={"LV","SC","REF","OTDT"}),AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">0.17"),0)))) |
| AB17:AB20 | AB17 | =IF(AC17="",0,IF(AC17="RDO",8,IF(AC17="10LIST",(AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">2.00")-$AA17),IF(OR(AC17={"LV","SC","REF","OTDT"}),AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">0.17"),0)))) |
| AI17:AI20 | AI17 | =IF(AJ17="",0,IF(AJ17="RDO",8,IF(AJ17="10LIST",(AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">2.00")-$AH17),IF(OR(AJ17={"LV","SC","REF","OTDT"}),AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">0.17"),0)))) |
| AP17:AP20 | AP17 | =IF(AQ17="",0,IF(AQ17="RDO",8,IF(AQ17="10LIST",(AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">2.00")-$AO17),IF(OR(AQ17={"LV","SC","REF","OTDT"}),AVERAGEIFS($E17:$FI17,$E$16:$FI$16,"OT W",$E17:$FI17,">0.17"),0)))) |
