Calculate % utilization 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'

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

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!

Jason Tarpinian

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

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.

Omkar

Hi Jason,

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