Trying to calculate an "on this day" capacity

Options

Hi There,


I have a master sheet of multiple projects. I am trying to work out how an allocated resource can show if it is over capacity in a column - similar to the resource view but so it shows in the sheet itself.


So If I have


Task

Start Date

End Date

Duration

Assigned to

Allocation %

Hours per day


I want to look up the allocation % between the start date and end date for [assigned to] and then do a lookup of all Rows where assigned to resource is mentioned and calculate the allocation %


1 Jan - 5 Jan, Task 1, Resource 1, 50% allocation - Column that shows Total allocation 150%

1 Jan - 3 Jan, Task 2, Resource 1, 100% allocation - Column that shows Total allocation 150%


I just cannot work out the formula to do this


Thanks,

Martin

Tags:

Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/11/21 Answer ✓
    Options

    Hi @Martin Hammond

    Hope you are fine, as per your question the following formula will do what you need

    =SUMIFS([Allocation %]:[Allocation %], [Assigned to]:[Assigned to], [Assigned to]@row)

    but as a planning Engineer, i can say to you this is not correct because in the above example you can see that the Total allocation for Resource 1 is 150% only from ( 1-Jan To 3-Jan ) but from ( 4 To 5 Jan ) his allocation is 50% only. so you need to think in a different approach.

    maybe you need to do a summary table in a helper sheet contain all Resource in your project in a rows and the day of the month in the columns and calculate for each day if the Resource is overallocated or not.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Hammond
    Answer ✓
    Options

    If it is of help to anyone I worked out how it correctly displays for me:


    =SUMIFS([Allocation %]:[Allocation %], [Start Date]:[Start Date], >=[Start Date]@row, [End Date]:[End Date], <=[End Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/11/21 Answer ✓
    Options

    Hi @Martin Hammond

    Hope you are fine, as per your question the following formula will do what you need

    =SUMIFS([Allocation %]:[Allocation %], [Assigned to]:[Assigned to], [Assigned to]@row)

    but as a planning Engineer, i can say to you this is not correct because in the above example you can see that the Total allocation for Resource 1 is 150% only from ( 1-Jan To 3-Jan ) but from ( 4 To 5 Jan ) his allocation is 50% only. so you need to think in a different approach.

    maybe you need to do a summary table in a helper sheet contain all Resource in your project in a rows and the day of the month in the columns and calculate for each day if the Resource is overallocated or not.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Hammond
    Options

    Thanks Basam that has helped a lot and given me food for thought on how to display capacity

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Martin Hammond

    You are welcome

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Martin Hammond
    Options

    Actually taking a look it the formula doesn't contain the date ranges so I do not think it would be correct, it would repeat the same sum regardless as it would not know what date range to search in

  • Martin Hammond
    Answer ✓
    Options

    If it is of help to anyone I worked out how it correctly displays for me:


    =SUMIFS([Allocation %]:[Allocation %], [Start Date]:[Start Date], >=[Start Date]@row, [End Date]:[End Date], <=[End Date]@row, [Assigned To]:[Assigned To], [Assigned To]@row)