Resource Allocaiton Based on Priorities & Other Conditions

J

Jalpit

New Member
Joined
Sep 29, 2014
Messages
1
[TABLE="width: 1008"]
<TBODY>[TR]
[TD="colspan: 4"]Table 1: Resource Requirement Nomination</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE</SPAN>
[/TD]
[TD]Nomination A</SPAN>
[/TD]
[TD]Nomination B</SPAN>
[/TD]
[TD]Nomination C</SPAN>
[/TD]
[TD]Nomination D</SPAN>
[/TD]
[TD]Nomination E</SPAN>
[/TD]
[TD]Nomination F</SPAN>
[/TD]
[TD]Nomination G</SPAN>
[/TD]
[TD]Nomination H</SPAN>
[/TD]
[TD]Total Resource Requriement Nomination </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1-Aug-14</SPAN>
[/TD]
[TD]30,000 </SPAN>
[/TD]
[TD]25,278 </SPAN>
[/TD]
[TD]4,000.00 </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]59,278 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-Aug-14</SPAN>
[/TD]
[TD]30,000 </SPAN>
[/TD]
[TD]19,165 </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]1,985.00 </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]51,150 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-Aug-14</SPAN>
[/TD]
[TD]30,000 </SPAN>
[/TD]
[TD]20,150 </SPAN>
[/TD]
[TD]24,850.00 </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]75,000 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Table 2: Actual Allocation of Resource Requirement</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PRIORITY #</SPAN>
[/TD]
[TD]1</SPAN>
[/TD]
[TD]2</SPAN>
[/TD]
[TD]4</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD]6</SPAN>
[/TD]
[TD]7</SPAN>
[/TD]
[TD]3</SPAN>
[/TD]
[TD]8</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE</SPAN>
[/TD]
[TD]Allocation A</SPAN>
[/TD]
[TD]Allocation B</SPAN>
[/TD]
[TD]Allocation C</SPAN>
[/TD]
[TD]Allocation D</SPAN>
[/TD]
[TD]Allocation E</SPAN>
[/TD]
[TD]Allocation F</SPAN>
[/TD]
[TD]Allocation G</SPAN>
[/TD]
[TD]Allocation H</SPAN>
[/TD]
[TD]TOTAL Allocation</SPAN>
[/TD]
[TD]Total Actual Resource</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1-Aug-14</SPAN>
[/TD]
[TD]30,000 </SPAN>
[/TD]
[TD]25,278 </SPAN>
[/TD]
[TD]4,000.00 </SPAN>
[/TD]
[TD]2,511.00 </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]- </SPAN>
[/TD]
[TD]61,789 </SPAN>
[/TD]
[TD]61,789 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2-Aug-14</SPAN>
[/TD]
[TD]30,000 </SPAN>
[/TD]
[TD]19,165 </SPAN>
[/TD]
[TD]1,038.00 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1,985.00 </SPAN>
[/TD]
[TD][/TD]
[TD]52,188 </SPAN>
[/TD]
[TD]52,188 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3-Aug-14</SPAN>
[/TD]
[TD]30,000 </SPAN>
[/TD]
[TD]20,150 </SPAN>
[/TD]
[TD]24,222.00 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]74,372 </SPAN>
[/TD]
[TD]74,372 </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hi There,</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 10"]I am trying to allocate resource based on priorities set out in table # 2. Following are the rules / terms & condition for the actual resource allocation </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]I need a formula which incorporate following of the both case on any day. </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]If some one can help me that would be really great?. If you need further information please let me know.</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]Table 2 is created with manual calculation. I was trying to derive result of table 2 using following formula but it has certain limitations:</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Formula Used: </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 5"]MAX(0,MIN(C5,$M12-SUMIF($C$10:$J$10,"<"&C$10,$C5:$J5)))</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Problem with Formula:</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 4"]Does not allocate excess resource to next priority </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rules:</SPAN>
[/TD]
[TD="colspan: 5"]Either of following cased can occur on particular day</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 8"]Allocation of total actual resource amongst different parties based on the resource requirement nomination </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]Allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]Allocation of actual resource amongst different parties are up to their requirement nomination only</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]i.e.: If there is resource requirement of 30,000 allocate actual resource up to 30,000</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]Case 1: Surplus Scenario (Actual Total > Nomination Total)</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 5"]Total allocation amongst parties should not exceed total actual resource</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]Allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]Allocation of actual resource amongst different parties are up to their requirement nomination only</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]If there is any Excess Resource i.e. Total Actual Resource > Total Resource Requirement Nomination </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]e.g. Date: 1-Aug-14 & 2-Aug-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 13"]After allocating Priority 1 & 2 there is still availability of resource but Priority 3 has not resource requirement nomination and priority 4 has nominated then allocation should be made to priority 4</SPAN>
[/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 11"]Still if there is any excess / surplus amount it should go to the next priority (i.e. Priority 5) even if they haven't forecast/ nominated the requirement of the day</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]e.g. Date: 1-Aug-14 & 2-Aug-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]After such allocation once total actual has distributed every other priority do not receive any thing</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]e.g. Priority 6,7,8 should be 0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]Case 2: Shortfall Scenario (Actual Total < Nomination Total)</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 5"]Total allocation amongst parties should not exceed total actual resource</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]Allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]Allocation of actual resource amongst different parties are up to their requirement nomination only</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]if there is any shortage i.e. Total Actual resource < Total Resource Requirement Nomination </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 8"]If such shortage exit, allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 10"]After allocating Priority 1 & 2 there is still resource available but Priority 3 has not nominated their resource requirement. i.e. Priority 3 should be 0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]e.g. Date: 3-Aug-14</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 9"]Still if there is any available actual resource, it should allocated to next priority if they have any nominated requirements.</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]i.e. Priority 4 should got the allocation. And such allocation will be less then what nominated</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 7"]After such allocation once total actual has distributed every other priority do not receive any thing</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]></SPAN>
[/TD]
[TD="colspan: 3"]e.g. Priority 5,6,7,8 should be 0</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kind Regards</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jalpit</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
You must log in or register to reply here.

Similar threads

O
  • Question Question
Excel/VBA to conditionally send emails per schedule
Replies
0
Views
1K
OC2PS
O
O
  • Question Question
Dynamically update a sheet based on values received in another sheet (within same workbook) from a form
Replies
0
Views
433
OC2PS
O
S
  • Question Question
Index Match Two-Way Lookup Help
Replies
8
Views
730
Peter_SSs
Peter_SSs
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
490
Pingaware
P
L
  • Question Question
Automatically import select number of rows and columns based on user-input from .txt file using VBA
Replies
5
Views
1K
Micron
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top