| Table 1: Resource Requirement Nomination</SPAN> | |||||||||||||
| DATE</SPAN> | Nomination A</SPAN> | Nomination B</SPAN> | Nomination C</SPAN> | Nomination D</SPAN> | Nomination E</SPAN> | Nomination F</SPAN> | Nomination G</SPAN> | Nomination H</SPAN> | Total Resource Requriement Nomination </SPAN> | ||||
| 1-Aug-14</SPAN> | 30,000 </SPAN> | 25,278 </SPAN> | 4,000.00 </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | 59,278 </SPAN> | ||||
| 2-Aug-14</SPAN> | 30,000 </SPAN> | 19,165 </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | 1,985.00 </SPAN> | - </SPAN> | 51,150 </SPAN> | ||||
| 3-Aug-14</SPAN> | 30,000 </SPAN> | 20,150 </SPAN> | 24,850.00 </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | 75,000 </SPAN> | ||||
| Table 2: Actual Allocation of Resource Requirement</SPAN> | |||||||||||||
| PRIORITY #</SPAN> | 1</SPAN> | 2</SPAN> | 4</SPAN> | 5</SPAN> | 6</SPAN> | 7</SPAN> | 3</SPAN> | 8</SPAN> | |||||
| DATE</SPAN> | Allocation A</SPAN> | Allocation B</SPAN> | Allocation C</SPAN> | Allocation D</SPAN> | Allocation E</SPAN> | Allocation F</SPAN> | Allocation G</SPAN> | Allocation H</SPAN> | TOTAL Allocation</SPAN> | Total Actual Resource</SPAN> | |||
| 1-Aug-14</SPAN> | 30,000 </SPAN> | 25,278 </SPAN> | 4,000.00 </SPAN> | 2,511.00 </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | - </SPAN> | 61,789 </SPAN> | 61,789 </SPAN> | |||
| 2-Aug-14</SPAN> | 30,000 </SPAN> | 19,165 </SPAN> | 1,038.00 </SPAN> | 1,985.00 </SPAN> | 52,188 </SPAN> | 52,188 </SPAN> | |||||||
| 3-Aug-14</SPAN> | 30,000 </SPAN> | 20,150 </SPAN> | 24,222.00 </SPAN> | 74,372 </SPAN> | 74,372 </SPAN> | ||||||||
| Hi There,</SPAN> | |||||||||||||
| 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> | |||||||||||||
| I need a formula which incorporate following of the both case on any day. </SPAN> | |||||||||||||
| If some one can help me that would be really great?. If you need further information please let me know.</SPAN> | |||||||||||||
| 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> | |||||||||||||
| Formula Used: </SPAN> | |||||||||||||
| ></SPAN> | MAX(0,MIN(C5,$M12-SUMIF($C$10:$J$10,"<"&C$10,$C5:$J5)))</SPAN> | ||||||||||||
| Problem with Formula:</SPAN> | |||||||||||||
| ></SPAN> | Does not allocate excess resource to next priority </SPAN> | ||||||||||||
| Rules:</SPAN> | Either of following cased can occur on particular day</SPAN> | ||||||||||||
| ></SPAN> | Allocation of total actual resource amongst different parties based on the resource requirement nomination </SPAN> | ||||||||||||
| ></SPAN> | Allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN> | ||||||||||||
| ></SPAN> | Allocation of actual resource amongst different parties are up to their requirement nomination only</SPAN> | ||||||||||||
| i.e.: If there is resource requirement of 30,000 allocate actual resource up to 30,000</SPAN> | |||||||||||||
| Case 1: Surplus Scenario (Actual Total > Nomination Total)</SPAN> | |||||||||||||
| ></SPAN> | Total allocation amongst parties should not exceed total actual resource</SPAN> | ||||||||||||
| ></SPAN> | Allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN> | ||||||||||||
| ></SPAN> | Allocation of actual resource amongst different parties are up to their requirement nomination only</SPAN> | ||||||||||||
| ></SPAN> | If there is any Excess Resource i.e. Total Actual Resource > Total Resource Requirement Nomination </SPAN> | ||||||||||||
| e.g. Date: 1-Aug-14 & 2-Aug-14</SPAN> | |||||||||||||
| ></SPAN> | 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> | ||||||||||||
| ></SPAN> | 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> | ||||||||||||
| e.g. Date: 1-Aug-14 & 2-Aug-14</SPAN> | |||||||||||||
| ></SPAN> | After such allocation once total actual has distributed every other priority do not receive any thing</SPAN> | ||||||||||||
| e.g. Priority 6,7,8 should be 0</SPAN> | |||||||||||||
| Case 2: Shortfall Scenario (Actual Total < Nomination Total)</SPAN> | |||||||||||||
| ></SPAN> | Total allocation amongst parties should not exceed total actual resource</SPAN> | ||||||||||||
| ></SPAN> | Allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN> | ||||||||||||
| ></SPAN> | Allocation of actual resource amongst different parties are up to their requirement nomination only</SPAN> | ||||||||||||
| ></SPAN> | if there is any shortage i.e. Total Actual resource < Total Resource Requirement Nomination </SPAN> | ||||||||||||
| ></SPAN> | If such shortage exit, allocation based on Priority numbers i.e. 1 should go first, 2 should go second and so on </SPAN> | ||||||||||||
| ></SPAN> | 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> | ||||||||||||
| e.g. Date: 3-Aug-14</SPAN> | |||||||||||||
| ></SPAN> | Still if there is any available actual resource, it should allocated to next priority if they have any nominated requirements.</SPAN> | ||||||||||||
| ></SPAN> | i.e. Priority 4 should got the allocation. And such allocation will be less then what nominated</SPAN> | ||||||||||||
| ></SPAN> | After such allocation once total actual has distributed every other priority do not receive any thing</SPAN> | ||||||||||||
| ></SPAN> | e.g. Priority 5,6,7,8 should be 0</SPAN> | ||||||||||||
| Kind Regards</SPAN> | |||||||||||||
| Jalpit</SPAN> |
<TBODY>
</TBODY>
