CODEWITHSUNDEEP
excelfunctionexcel-formulacumulative-sum
sheltered harbor
sheltered harbor

Reputation: 1

Cumulative difference formula

I have a a table that calculates the optimal cost allocation against a specific resource by hour and then distributes that data based on the results. The allocation works wonders but my issue lies when there are multiple rows that have the same hour. I have not been able to create a formula in Excel that can take into account previous rows and then subtract the amount used from the available resource. If all of a resource has been used up, it should default to the next available resource. Here is the current formula:

=IF(Q2=0,"NONE",INDEX('RESOURCE ALLOCATION'!$F$3:$I$3,1,MATCH(LARGE(INDIRECT("'RESOURCE ALLOCATION'!F"&B2+3&" :I"&B2+3),1),INDIRECT("'RESOURCE ALLOCATION'!F"&B2+3&" :I"&B2+3),0)))

Q2 = The first cell with a number in it

Resource Allocation is a worksheet in this workbook where the range $F$3:$I$3 are the available resources by name.

The MATCH formula simply looks at the current row's hour, points it to the corresponding row in the table and then returns the associated resource by name.

Each subsequent row increases by one like so:

=IF(Q3=0,"NONE",INDEX('RESOURCE ALLOCATION'!$F$3:$I$3,1,MATCH(LARGE(INDIRECT("'RESOURCE ALLOCATION'!F"&B3+3&" :I"&B3+3),1),INDIRECT("'RESOURCE ALLOCATION'!F"&B3+3&" :I"&B3+3),0)))

The table I am using looks like:

Hour Resource 1 Resource 2  Resource 3  Resource 4    TOTAL

  21         50          0           0           10    60

For this hour, there are 60 units required. 50 allocated against Resource 1 and 10 allocated against Resource 4. Under the current formula it just allocates everything against Resource 1.

Upvotes: 0

Views: 5116

Answers (1)

user3927220
user3927220

Reputation:

This is my solution:

Solution

I have added a few additional columns for legibility.

In column C you have simple sumifs checking amount spent for this particular hour for a given cell and all the preceding ones.

Formula in C2:

=SUMIF($A$2:A2,A2,$B$2:B2)

Column D is just range that will go into INDIRECT function.

Formula in D2:

="I"&MATCH(A2,H:H,0)&":L"&MATCH(A2,H:H,0)

Column E shows a resource that has been used as the 1st source for this particular hour and its occurrence.

Formula in E2:

=5-IFERROR((1/(A2=A1))*IFERROR(MATCH(C1,INDIRECT(D2),0)-1,MATCH(C1,INDIRECT(D2),-1)),4)

Column F shows a resource that has been used as the last source for this particular hour and its occurrence. Returns error if there are not enough resources (I do not know if it might be the case).

Formula in F2:

=5-MATCH(C2,INDIRECT(D2),-1)

Summary:

The idea is quite simple but if requires you to change your approach. The accumulation should occur within the resources rather than in the final report table. R4 should be a sum of actual resource 4 and all the other resources i.e. R4 = resource 4 + resource 3 + resource 2 + resource 1, R3 = resource 3 + resource 2 + resource 1, R2 = resource 2 + resource 1, R1 = resource 1. And having that simply use matching with argument -1.

Since you don't have all the resources you might want to add some kind of exclusion to the range that is being reported as the 1st and the last used resources.

Upvotes: 1

Related Questions