Spreadsheet: 878921d1726247942-nested-formula-to-handle-8-scenarios-integrations-hours-devfromcorrie-
Sheet: Integration

ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | TESTING APPROACH |  |  |  |  |  |  |  |  |  | WORKING OUT TABLE All Scenarios
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | STATUS Developer - recompile [NAME] - data set-up |  |  | SIT Status | Test Run Complexity | Resource | Time Required for completion (hrs) | Remaining time (hrs)  *Sample values* |  |  | Col AC STATUS Developer - recompile [NAME] - data set-up status | Col AF SIT Status | Col AH Resource | Col AJ Remaining time *The nested formulae will need to handle all below scenarios*
 |  |  |  |  | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | SMT
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | Not Started | ANON | ANON | Not Started | 3 | Mark |  |  | 3hrs | scenario1 | Not started | Not started | [NAME] or Any Developer | In this scenario reference cell AI* as no work has started
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | WIP | ANON | ANON | Not Started | 3 | Developer 2 |  |  | 2.5hrs | [NAME] | WIP | Not started | Any Developer | Using the adjacent cell in AG as the lookup value, use the developer table in the lookup tab as the table array, column index 3rd column "G". Divide the lookup value by two and add the value in column H. Divide by 60 to give hours
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | Complete | ANON | ANON | Not Started | 4 | Developer 8 |  |  | 4hrs | scenario3 | Complete | Not started | Any Developer | Using the adjacent cell in AG as the lookup value, use the developer table in the lookup tab as the table array, column index 3rd column "G". Subtract this value from the total hours in Col I in developer table. Divide by 60 to give hours
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | Complete | ANON | ANON | WIP | 5 | Developer 1 |  |  | 6.75hrs | scenario4 | Complete | WIP | Any Developer | Using the adjacent cell in AG as the lookup value, use the developer table in the lookup tab as the table array, column index 3rd column "H". Divide the lookup value by two and add the value in column G. Divide by 60 to give hours
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | Complete | ANON | ANON | Complete | 2 | Developer 6 |  |  | 0hrs | scenario5 | Complete | Complete | [NAME] or Any Developer | In this scenario the remaining time would be 0hrs as all test work completed
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | WIP | ANON | ANON | Not Started | 5 | Mark |  |  | 6hrs | scenario6 | WIP | Not started | Mark | Using the adjacent cell in AG as the lookup value, use the mark table in the lookup tab as the table array, column index 3rd column "N". Divide the lookup value by two and add the value in column O. Divide by 60 to give hours
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | Complete | ANON | ANON | Not Started | 4 | Mark |  |  | 3hrs | scenario7 | Complete | Not started | Mark | Using the adjacent cell in AG as the lookup value, use the mark table in the lookup tab as the table array, column index 3rd column "N". Subtract this value from the total hours in Col P in mark table. Divide by 60 to give hours
ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | ANON | Complete | ANON | ANON | WIP | 3 | Mark |  |  | 2.33333333333333hrs | scenario8 | Complete | WIP | Mark | Using the adjacent cell in AG as the lookup value, use the [NAME] table in the lookup tab as the table array, column index 3rd column "O". Divide the lookup value by two and add the value in column N. Divide by 60 to give hours