Trying to count backwards to get a planned percentage

[neowok]

Ok this is a bit hard to explain so I have attached a sample file which shows how far I have managed to get so far.

Basically I have this file which lists planned percentages for some work. We split the year into periods and we want to have a planned vs actual percentage for the contractor at the end of each period.

This is all easy enough.....except if the contractor loses a shift through no fault of their own, we need to adjust the planned percentage up by 1 day accordingly to get the percentage they should REALLY be on. The difficulty is when adjusting this upwards by however many lost shifts they have had to-date at the end of the given period, it also needs to account for the non-working shifts (bank holidays etc plus any specific ones we add) so that it points at the correct percentage. I have added a column to assist with this just with a 1 in each non working shift.

So what I have got it to do is add up all the lost shifts for that period (say 10 for example), count upwards by that number and add up any non-working shifts in that 10 cell range (say 3 for example). Then add those together, go up the planned percentage column by that many cells from the period end date (which would be 13) and that is then our new adjusted planned percentage taking into account the lost shifts. The problem with this is if those extra 3 cells happen to be non working shifts, it will not count these because it has already counted the non-working shifts over the original 10 cell range, so we end up with the wrong percentage.

using the sample file, if I mark every shift ('tracking sleepers' sheet, column M) as a lost shift during period 1 except for 1st april, then you would expect the planned percentage to come out at 10% because only 1 shift was not lost and the first shift planned percentage is 10%. However because it has not counted all the non-working shifts correctly, it is showing 14% (see "period planned percentages" sheet) which would be 5 shifts that were not lost. My formula to calculate this is in the period planned percentages sheet column D. It is this formula I am trying to correct so that it will accurately take into account the lost shifts as of whatever the period end date is, and correctly take into account the non-working shifts to give the correct planned percentage against each period. As it stands it very nearly works, it is just not quite counting the non-working shifts correctly.

See, told you it was hard to explain lol

thanks to anyone that managed to even work out what it is trying to do

[neowok]

nobody

perhaps a user defined function of some kind would do it?