CODEWITHSUNDEEP
excelschedule
masterofimps
masterofimps

Reputation: 49

Utilisation Scheduling (Distributing Values)

I have a simple utilization table in excel (percentages based on booked hours as a percentage of available hours) where percentages over 100% indicate hours booked. Eg.

example of simple schedule

However as each job can go on past the month end, I need something like a 300% to be spread to 100% over 3 months to indicate the following 3 months completely booked. Eg.

enter image description here

While I can easily think of an iterative method I could put together in VBA, in this case I can only use non macro enabled workbooks. I can get to a point where I can deal with the situation where there is a single large value, and I can distribute out over the next number of months, but Im stuck on consecutive large values and spreading them out accurately.

How can I achieve the result in the second example without using VBA ? Any help or pointers in the right direction would be much appreciated!

Upvotes: 2

Views: 40

Answers (1)

ELW
ELW

Reputation: 380

The simplest way I can think of to do it is have a second table (in this case, it's in row 10) to sum up the current and preceding months' values in the original table and subtract the preceding months' values from the second table.

So in cell B10 referring to the original table in row 2:

=IF(SUM($A2:B2)-SUM($A10:A10)>1,1,SUM($A2:B2)-SUM($A10:A10))

Where January is in Cell B1, the original totals start in cell B2, and the formula begins in B10 for January. $A2:B2 refers to the current and prior months' data in the original table and $A10:A10 is the total of preceding months in the second/allocated table.

It might help to post your formula for taking care of the single large value.

Upvotes: 2

Related Questions