Calculate % utilization of the resources based on Project Type, Current Project Duration

Options

Hi,


Sheet 1 - Contains following columns


- Resource Name

- Project Type

- Project Duration (Week Number)


Sheet 2 - Contains the following columns


- Project Type

- Project Duration (Week Number)

- % Allocation

- % allocation is different for different combinations of Project Type and Project Duration (Week Number)


Ex. For Project Type A - % allocation is


15% if project duration is week 1

13% if project duration is Week 2

12% if project duration is week 3.

For Project Type B - % allocation is


25% if project duration is week 1

23% if project duration is Week 2

22% if project duration is week 3

For Project Type C - % allocation is


35% if project duration is week 1

33% if project duration is Week 2

32% if project duration is week 3.


I am trying to calculate % allocation for each project on 'Sheet 1' by referring to project type, project duration. It should return the appropriate % allocation value by referring to 'Sheet 2'

---------------------------------------------

Tried this formula and it seems to be working.


=INDEX({Sheet 2 - % Allocation}, MATCH([Project Type]@row, {Sheet 2 - Project Type}, 0) + MATCH([Project Duration]@row, {Sheet 2 - Project Duration}, 0) - 1)


Please review and advise if you have any other ideas. Thank you!

Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    It sounds like you're trying to INDEX off of multiple criteria, so you'll need to use the COLLECT function to customize your INDEX range. If I can do it in my head with your variables, it'd be:

    =INDEX(COLLECT({Sheet 2 - % Allocation}, {Sheet 2 - Project Type}, ([Project Type]@row, {Sheet 2 - Project Duration} [Project Duration]@row),1)

    The last 1 in the INDEX formula assumes that there will only ever be one combination of Project Type/Duration to return.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    Answer ✓
    Options

    It sounds like you're trying to INDEX off of multiple criteria, so you'll need to use the COLLECT function to customize your INDEX range. If I can do it in my head with your variables, it'd be:

    =INDEX(COLLECT({Sheet 2 - % Allocation}, {Sheet 2 - Project Type}, ([Project Type]@row, {Sheet 2 - Project Duration} [Project Duration]@row),1)

    The last 1 in the INDEX formula assumes that there will only ever be one combination of Project Type/Duration to return.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • Omkar
    Omkar ✭✭
    Options

    Hi Jason,

    Brilliant! This worked. Thanks a lot for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!