At work I have been asked to look at the productivity of our process and have been given an excel file they have used to calculate this for years. Currently a calculator is used alongside the sheet which seems barmy to me, but I am struggling to get excel to do what I want.
Basically our process has runs and within each run there can be one or more sequences and it is this variation in the number of sequences, which creates varying numbers of rows per run. We have a total materials used value, and this needs to be proportioned between each sequence in a run, the only way I can see to do this is to have the total run value entered into each row for the formula to reference, this works but looks messy and was wandering if there was a better way to do it. The problem I can see with it is that if the total is put into the first row aof a run, there can be up to around 20 sequences in each run, so referencing this single total in each subsequent row is hard as all runs are entered into the same sheet, so absolute reference do no work as in the next run, a different number needs to be referenced.
Sorry for the rambling, but it is not all that easy ot explain!
If you need more specifics, I can provide them