Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question on formula of matching Project Workload vs Resource Capac
Hello.
I'd like to ask for some ideas on how to do the following. I'm trying to build a report that will match multiple projects and required "workload days needed" against multiple "workload available" resources. A simple layout of the Excel would be in 2 blocks of data. 1st is: Project, Project Ranking (what should be done first), resource assigned, project days needed and Can it be done? determination. Project Ranking Resource Project Days Needed Can Be Done? A 1 Manuel 10 Yes B 2 Cindi 10 Yes C 3 Cindi 10 Yes D 4 Cindi 10 Yes E 5 Cindi 10 Yes F 6 Maynard 10 Yes G 7 Maynard 10 Yes H 8 Maynard 10 No I 9 Cindi 10 No The second table matches Project Days needed against available resources. Resource Project Days Available Project Days Needed Delta Manuel 20 10 10 Cindi 40 50 -10 Maynard 20 30 -10 I can get the days needed from the sumif function. My questions: 1) What would be the formula to write for the "Can Be Done?" column in the first block of data? I have a concept of tracking the cumulative days needed by person to compare against days available... but I'm stuck on the formula. 2) Would the resulting formula recommended be robust enough if I had to resort the rankings? 3) Can this be done in a pivot table? Thanks to this great community for all its help! -- Thanks, Morocco Mole |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question on formula of matching Project Workload vs Resource Capac
HI Morroco,
I set this up exactly as you have described it on a spreadsheet. I have made some assumptions. The ranking determines the order of the projects. The second table is on the same spreadsheet. You can adjust the formula if it is not. First, I have placed all the headers in the first table in row 1 starting at A1 to E1. The second table starts at H1 to K1. I used the information you gave so you may need to make adjustments to your actual size. You will need a "counter" set up in column F. You can hide this column later. At F2 place (without the "") "=COUNTIF($C$2:C2,C2)" Copy-drag this formula down as far as you need it to go. In your 1st table, row 10 will have the last formula and it will look like "=COUNTIF($C$2:C10,C10)" You should see numbers counting in column F. 1, 1, 2, 3, 4, 1, 2, 3, 5. What this does is count the number of times each name has appeared down the list. In cell E2 place "=IF(D2*F2<=(VLOOKUP(C2,($H$2:$I$4),2,FALSE)),"yes ","no")" Copy-drag this down to the bottom of your list. The part "$H$2:$I$4" in the formula is the range of the information in your second table from the Resource to the Project Days Available. So if you have more people you will need to adjust the "$I$4" part. Let me know how it works. Squeaky "Morocco Mole" wrote: Hello. I'd like to ask for some ideas on how to do the following. I'm trying to build a report that will match multiple projects and required "workload days needed" against multiple "workload available" resources. A simple layout of the Excel would be in 2 blocks of data. 1st is: Project, Project Ranking (what should be done first), resource assigned, project days needed and Can it be done? determination. Project Ranking Resource Project Days Needed Can Be Done? A 1 Manuel 10 Yes B 2 Cindi 10 Yes C 3 Cindi 10 Yes D 4 Cindi 10 Yes E 5 Cindi 10 Yes F 6 Maynard 10 Yes G 7 Maynard 10 Yes H 8 Maynard 10 No I 9 Cindi 10 No The second table matches Project Days needed against available resources. Resource Project Days Available Project Days Needed Delta Manuel 20 10 10 Cindi 40 50 -10 Maynard 20 30 -10 I can get the days needed from the sumif function. My questions: 1) What would be the formula to write for the "Can Be Done?" column in the first block of data? I have a concept of tracking the cumulative days needed by person to compare against days available... but I'm stuck on the formula. 2) Would the resulting formula recommended be robust enough if I had to resort the rankings? 3) Can this be done in a pivot table? Thanks to this great community for all its help! -- Thanks, Morocco Mole |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question on formula of matching Project Workload vs Resource C
Awesome. That worked. Thanks Squeeky.
-- Thanks, Morocco Mole "Squeaky" wrote: HI Morroco, I set this up exactly as you have described it on a spreadsheet. I have made some assumptions. The ranking determines the order of the projects. The second table is on the same spreadsheet. You can adjust the formula if it is not. First, I have placed all the headers in the first table in row 1 starting at A1 to E1. The second table starts at H1 to K1. I used the information you gave so you may need to make adjustments to your actual size. You will need a "counter" set up in column F. You can hide this column later. At F2 place (without the "") "=COUNTIF($C$2:C2,C2)" Copy-drag this formula down as far as you need it to go. In your 1st table, row 10 will have the last formula and it will look like "=COUNTIF($C$2:C10,C10)" You should see numbers counting in column F. 1, 1, 2, 3, 4, 1, 2, 3, 5. What this does is count the number of times each name has appeared down the list. In cell E2 place "=IF(D2*F2<=(VLOOKUP(C2,($H$2:$I$4),2,FALSE)),"yes ","no")" Copy-drag this down to the bottom of your list. The part "$H$2:$I$4" in the formula is the range of the information in your second table from the Resource to the Project Days Available. So if you have more people you will need to adjust the "$I$4" part. Let me know how it works. Squeaky "Morocco Mole" wrote: Hello. I'd like to ask for some ideas on how to do the following. I'm trying to build a report that will match multiple projects and required "workload days needed" against multiple "workload available" resources. A simple layout of the Excel would be in 2 blocks of data. 1st is: Project, Project Ranking (what should be done first), resource assigned, project days needed and Can it be done? determination. Project Ranking Resource Project Days Needed Can Be Done? A 1 Manuel 10 Yes B 2 Cindi 10 Yes C 3 Cindi 10 Yes D 4 Cindi 10 Yes E 5 Cindi 10 Yes F 6 Maynard 10 Yes G 7 Maynard 10 Yes H 8 Maynard 10 No I 9 Cindi 10 No The second table matches Project Days needed against available resources. Resource Project Days Available Project Days Needed Delta Manuel 20 10 10 Cindi 40 50 -10 Maynard 20 30 -10 I can get the days needed from the sumif function. My questions: 1) What would be the formula to write for the "Can Be Done?" column in the first block of data? I have a concept of tracking the cumulative days needed by person to compare against days available... but I'm stuck on the formula. 2) Would the resulting formula recommended be robust enough if I had to resort the rankings? 3) Can this be done in a pivot table? Thanks to this great community for all its help! -- Thanks, Morocco Mole |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Project question????????????????? | Excel Discussion (Misc queries) | |||
data linking/field matching/formula updating long question | Excel Discussion (Misc queries) | |||
random assignments of workload to set # of people | Excel Worksheet Functions | |||
When Excel Just Can't Hadle the Workload | Excel Discussion (Misc queries) | |||
Allocate workload evenly to different staff | Excel Worksheet Functions |