Calculate variable shift time based on production volumes

Hi,

I have attached a spread sheet which will hopefully explain what I'm trying to do...

I am trying to calculate the amount of shifts (and shift time) that would be required to complete a certain number of tasks (verifications). However I seem unable to get my formula's to correctly complete the total amount of time required as my manual calculations do not match my excel ones...

Cells in orange currently require user input and anything in blue is a calculated cell. The number of tasks a person is able to complete differs based on the length of a shift - i.e if you're working longer hours you would be less fast... This is calculated with a vlookup on the next sheet...

Any help much appreciated as I'm driving myself bonkers!!

Many thanks,



Kat

p.s - apologies for the rudimentary formulas - the longer I've spent trying to get this right the laxer they've gotten!!

should't there be 2500 in L5?

Hi,

Yes Tim (sorry) - the formula in cell L5 should have been =$K$1*0.05 - I had =$K$1*0.05 in there from when I was attempting to make the formulas works (10% was just is easier to work out in my head)!

Newdoverman - Cell 'K1' details the amount of work due to be completed per a job. The service level agreement we have states that on day 1 5% needs completing, the next 3 days we are required to do an additional 25% per a day and then on day 4 the final 20%...

Column 'I' & column 'J' don't do anything on the uploaded spread sheet - however they are linked through to another piece of work and are there for me to do a sumproduct and a weighted average of verifications completed per X period of shifts... That sheet is working fine and I thought it would be clearer to just upload the sheet where I am having problems...

I have uploaded the sheet again with a manual calc in red text - and based each 'verification' to the same figure on the automated bit (wiping the vlookup)... I have also changed the multiplier to be at 20% for each instance so they're all the same... It just makes it easier (I think and am happy to be corrected) to see what I am trying to achieve...

Many thanks,



Kat