Calculate capacity of the resources based on Project Type, Current Project Duration
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
-
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
-
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.
-
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!
-
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
-
Thanks a lot for the inputs. Yes, this worked. Greatly appreciated!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!