Calculate capacity of the resources based on Project Type, Current Project Duration

Omkar
Omkar ✭✭
edited 05/02/23 in Formulas and Functions

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'

Please assist. Thank you!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Omkar

    When you have two criteria to match (both the Project Duration and the Project Type), you will want to use the COLLECT function instead of MATCH.

    Try this:

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

    This will bring back the % Allocation that's showing in your second sheet based on the Project Type and Duration listed in your first sheet. Is that what you were looking to do?

    Cheers,

    Genevieve

Answers

  • Omkar
    Omkar ✭✭
    edited 05/02/23

    I used the following formula in Sheet 1 - Column '% Allocation'

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

    However, it is showing the values only when Project Duration is week 1 and showing as '#INVALID VALUE' for the rest.

  • Omkar
    Omkar ✭✭

    Update -

    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!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Omkar

    When you have two criteria to match (both the Project Duration and the Project Type), you will want to use the COLLECT function instead of MATCH.

    Try this:

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

    This will bring back the % Allocation that's showing in your second sheet based on the Project Type and Duration listed in your first sheet. Is that what you were looking to do?

    Cheers,

    Genevieve

  • Omkar
    Omkar ✭✭

    Thanks a lot for the inputs. Yes, this worked. Greatly appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!