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

Options
✭✭
edited 05/02/23

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'

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭
edited 05/02/23
Options

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.

• ✭✭
Options

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!

• Employee
Options

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