Showing skipped operations as complete on a job.

[#1]

I have an issue I am trying to solve.

I am trying to show percent completed for a job. However we sometimes have an issue with skipping operations.

My logic is that I am counting all the operations on the job, and then counting all the operations marked as complete to find out the percentage.

I have it working by using the previous command in the script retrieving the previous complete field and using this formula:

=sum(if(opcomplete=1 and next=0 and (jobnum&assemblyseq)=check,2,
if(opcomplete=1 and next=0 and (jobnum&assemblyseq)<>check,1,
if(opcomplete=1 and next=1,1
))))

Where opcomplete is the flag for completed, next (was trying to get the next record but gave up and did not change the field name yet) is the flag for the previous operation, and check is the previous records job and assembly (checks to see if this is the first record in the group)

It sets the field to 1 if the operation is complete, and 2 if the operation is complete and the previous is not. Taking the sum of this field then shows me the count for operations completed before the last one.

This works great in the cases where they skip one operation:

How ever there is a problem when they skip more than 1 operation:

I need it to count all the records before the most recent one as complete. So basically the 2 you see in the image needs to be a 8.

Any one know how to do this? I know I could create peek statements that look back several records, but I have no way of telling how many back I need to go.

What I almost need is a running total that sets a 1 if its not completed, but only summarizes if it runs into a record marked as complete.

[#2]

Any one have any ideas?