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

Omkar

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!

Genevieve P.

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

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

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.

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

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