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
    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

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
    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • 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!