Project Flight Calculator

[matt-gilbert]

Hi. I am trying to automate a spreadsheet to calculate manning levels per roster crew as per attached spreadsheet (range C24:V27). I have tried many types of formulas (COUNTIF, OFFSET etc) without much success. I'm thinking it might be too much for a formula given the variables involved. Any help/thoughts would be appreciated. Thanks in advance, Matt.

 

[Peter Bartholomew]

The basic logic of your problem can be captured as a matrix equation. The unknowns would be the manning level for each tour of each roster crew so the size of the matrix is equal to the number of weeks. The matrix calculation to turn manning levels by crew into total man weeks is a relatively simple matrix with 3 non-zero diagonals but to reverse the process requires matrix inversion. The inverse matrix will be a lower triangular in form and essentially implements an accumulation from left to right.

In terms of implementation, that means it should also be possible to determine the manning levels working step by step from left to right. For large arrays such an approach will provide a computationally more efficient solution though there are more formulas involved because any array solution would need to support breakup – something that CSE arrays do but modern Dynamic Arrays DA do not.

I have a feeling further explanation will be needed. have attached a preliminary look..

 

[vletm]

matt-gilbert
Why ...?
If week 2's D45 = blank & D46 is 5 ... why D24 is 5? and so on? ( of course, You have a formula there ... but why? )
= if no fights In or Out .. why some 24...27 rows should have values?
For me, if need/plan to fly, then plan to have crew or ... rest? ( You seems to do this opposite way or how? )
= maybe 45...48rows shows ... when need/plan to fly
as well as above (31..34, 38..41) rows would show which way to fly ... hmm?

 

[matt-gilbert]

The basic logic of your problem can be captured as a matrix equation. The unknowns would be the manning level for each tour of each roster crew so the size of the matrix is equal to the number of weeks. The matrix calculation to turn manning levels by crew into total man weeks is a relatively simple matrix with 3 non-zero diagonals but to reverse the process requires matrix inversion. The inverse matrix will be a lower triangular in form and essentially implements an accumulation from left to right.

In terms of implementation, that means it should also be possible to determine the manning levels working step by step from left to right. For large arrays such an approach will provide a computationally more efficient solution though there are more formulas involved because any array solution would need to support breakup – something that CSE arrays do but modern Dynamic Arrays DA do not.

I have a feeling further explanation will be needed. have attached a preliminary look..

Thank you for your detailed response Peter. You lost me after "matrix equation"... I have had a look at your attempt and note that it has negative values (S24, T24 etc) which is not correct. I was also hoping to not rely on "helper" formulas although I understand if this is not possible. Would be interested if you have a revised solution to solve the negative issue. Thanks again. Matt

 

[matt-gilbert]

matt-gilbert
Why ...?
If week 2's D45 = blank & D46 is 5 ... why D24 is 5? and so on? ( of course, You have a formula there ... but why? )
= if no fights In or Out .. why some 24...27 rows should have values?
For me, if need/plan to fly, then plan to have crew or ... rest? ( You seems to do this opposite way or how? )
= maybe 45...48rows shows ... when need/plan to fly
as well as above (31..34, 38..41) rows would show which way to fly ... hmm?

Hi vletm. The "Flights Required (return flights)" table only captures when a return flight is booked and paid ie C45. The "On-site Manning Levels per Roster Crew" table shows the number of men on site for each week on the project. So for roster crew 1, there will be 5 flights booked and paid (C45), then 5 men will "fly in" to site (C31), they will then work their 3 weeks on site (C24:E24) and then they will "fly out" from site (E38). I understand that the order of the rows is a little bit mixed up but this is the order that I calculated the formulas. Regards, Matt.

 

[vletm]

matt-gilbert
Your: I understand that the order of the rows is a little bit mixed up but this is the order that I calculated the formulas.
... then why do You use formulas, if this is even for You 'a little bit mixed up'?
... means ... why You won't solve those in 'order'?
Still that eg If week 2's D45 = blank & D46 is 5 ... why D24 is 5? if no plan?
... should there make a plan for needed hours or so?

 

[Peter Bartholomew]

"I was also hoping to not rely on "helper" formulas"
An advantage of the array formulas is that I could use a defined name to refer to each formula and it would then only be evaluated when the result is needed for a further formula. I didn't do this because my part solution is probably sufficiently obscure without hiding the calculation!

All the matrices do is define a set of equations that state that the overall manning level (known) is the sum of the individual crew levels. I solve the equations (using matrix inversion) to determine the crew levels. The mathematics is more advanced but it allows the solution to be expressed more simply - which doesn't help a bit if you are not familiar with the mathematics

"I … note that it has negative values (S24, T24 etc) which is not correct"
The calculation does not take into consideration the possibility of demobilising part of roster crew from site early so the manning levels calculated do not provide a valid solution. I do not know whether further manual input is needed to set the level and timing of such demobilisation or whether some rules could be introduced (I had the former in mind).

Note
Although I chose a top-down approach in which the equations determining the required manning levels per roster crew were formulated and solved, this problem, by its nature can be solved by accumulating the levels week by week,

• A newly rostered crew will be manned at a level to meet the overall needs.
• The second or third week will generally be the same as the previous week.
• The fourth week will be zero, with the newly-rostered team picking up the slack.

This would require some arithmetic modulo 4 to control the increment to the next week's figures.
Life 'down amongst the weeds' can also get somewhat hairy. I don't know which approach will turn out to be the 'simpler' overall.

p.s. I have attached a file that allows the user to determine the level of demobilisation required for the crew in their final week.

 

[Peter Bartholomew]

@hofiy
I suggest that this should be a new thread and it would be up to you to define the theoretical basis of any calculation you wish to perform.