Google Spreadsheet Calculating Work Overload

Question:

( by Ben )

Hello,

I hope someone is able to help - I will try and be as clear as I can with my question.

I am trying to make a basic machine capacity table in Google Spreadsheet, but need some help on how to do it. The concept is simple but I do not know how to make an equation fit it.

Please see this link for the Spreadsheet:

The WEEK NO. column is the number of the week in the year

The JOBS [WEEKS] column is how much work there is to do in weeks that needs to be started.

The MACHINIST column is how many machinist's are available that week.

What I am trying to do is essentially "count backwards" so I can see where I am overloaded with work. For example Week 32 there is 3.4 weeks of work but only 2 machinists. Which means that you need to do the 1.4 overload the week before, but that means that you can only do 0.6 of week 31's work because there is also only 2 machinists in that week. And all the work in week 31 will have to be pushed back again. So on and so forth.

The maximum amount of jobs that can be do in any given week is restricted by how many machinists are in that week, any overload needs to be done the week before otherwise it will be late. (1 machinist can do 1 weeks of jobs).

I may be approaching this problem the wrong way, so any suggestions will be appreciated. The shared Spreadsheet is editable so please feel free to change the data if you need to.

I look forward to any responses.

Many thanks,

Ben

Solution:

Have a look at the screenshot of my spreadsheet having solution:

I have the following custom formula in Cell D2:
=kishan(B2:C12)


This custom formula works on the basis of following script:

function kishan()
{
  if (arguments.length < 1)
    return "#NA Please input range";
  else if (arguments.length > 1)
    return "#NA Input Can't be more than one arguement";
  
  var values = arguments[0];
  var returnArray = [];
  
  for(i=values.length-1;i>=0;i--)
  {
    if(values[i][0]>values[i][1])
    {
      var temp = values[i][0] - values[i][1];
      values[i][0] = values[i][1];
      values[i-1][0] = values[i-1][0]+temp;
    }
    returnArray[i] = [values[i][0]];
  }
  return returnArray;
}

Put the above code in your script editor in your spreadsheet.

And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive