Finding the sum value of permutations...

[ANDY]

Hi There,

I have a project to find out the best possible total from a vast amount of possible permutations of where to load pallets in an aircraft and need some help.

Aircraft has 35 positions each of which has an index value which changes when a different weight is loaded; the value is either negative (front of the plane) or positive (back of the plane) and the idea is to find a good balance (the centre of gravity). So if I have 2 pallets at 2000kg each and put them both in the back the index will be a high positive and not good, if I put one pallet in the front and one in the back then this is balanced...I hope I have made the concept clear.

The problem is very clear to me...working with (let's say 3 pallets and an aircraft with only 4 positions the options are very limited (24 permutations) as per the attachment, but as I have 35 possible positions and anywhere from 1 to 35 pallets to deal with the permutations go off the scale.

Can anyone help me either in excel or access?

(The attchment shows pallet numbers(letters) in each position but these would be plus or minus numeric values in the real model and it is the total of each permutation (all positions added together) that I need to establish)

Cheers Andy

[mdmackillop]

Is it possible to post the indexing and typical weights. It would be easier to appreciate the result if we can see what is really happening. Also, while 0 may or may not be achievable, what is an acceptable maximum?

[ANDY]

Hi there,

No problem...in my first posting I kept it very simple but the actual is quite different. I hope the attached file will help but please let me know if you need more details. By the way the aircraft is best loaded (for tip prevention) with an index of -60 (=>0 means it would sit on its tail while loading).

I have shown only the main deck position with maximum weights (unshaded areas) and the pallet weight can be from 125 to the max on the scale.

Cheers Andy

[mdmackillop]

Hi Andy,
I did something like this once before. No time to code the VBA just now.
This approach is to rely on Chance to get the results.

Insert the pallet weights into column A
Sort the coloured panel by the random number
Does total meet the criteria?
If Yes, does each Position meet the criteria.
If Yes, result achieved
If No, resort on random values

[ANDY]

Many thank for taking the time to deal with my problem, unfortunately I don't think your new file addresses the problem.

I have re-posted the file and shown on sheet one a small matrix of 3 plts in 3 possible positions then underneath the 6 permutations and you can see that the last one (-18) is my best option (closest to -60). The problem is that the number of permutations for anywhere between 1 and 35 pallets being loaded in various combinations of position (total of 35 available, is too vast to consider using a matrix; therefore I need to do this with some sort of formula or code.

Any Ideas?

Thanks again for you time...

Cheers Andy

[mdmackillop]

Hi Andy,
The Chance approach tests combinations until a suitable one is found The grid produces indices for each pallet in each position (as I understand it).
To test manually
Sum the Index column
Select the coloured area
Click the A-Z sort button and the total will change with each sort. Code will do this very quickly until a target is met.
Although there are 10^39, there are also many possible solutions. We just need to find one of them.

[unmarkedhelicopter]

I can't work on this in Excel at the moment but I want to get somethings clear before I do.
I assume you have a MAXIMUM load ? do you EVER exceed this in a worst case pallet scenario ?
Your ideal Mass balance is -60, is that -60 kg m ? and what are your tolerances on this ? (i.e. if we can't acheive with x pallets we can then try with x-1 pallets ?)
I assume you'd like to keep the heaviest pallets near the centre to reduce both yaw momentum and stress on airframe ? or is this not a consideration ?
You say the aircraft has 24 pallet positions, are there ALWAYS 24 Pallets ? e.g. you only fly when you are full or are there always some pallets left over (i.e. one you can pick and choose from ?)

[ANDY]

Hi There,

AH...I begin to see the light...but I am still not 100% clear how this actually works. Could I be a real pain and ask you to assume we only have 3 pallets for a flight (A=1500 kilos, b=3000 kilos, c=5600 kilos) and show me where I should enter these?

Forgive my stupidity...

Cheers Andy

[ANDY]

Hi Unmarkedhelicopter!!!

The actual configuration is as follows...

Main deck = 26 positions max 70,000 kilos

Lower deck = 10 position (not shown for the example) max 35,000 kilos

But position AR has a ballast pallet locked in position (4050 kilos) therefore we have a maximum uplift of 35 pallets.

But the combined max weight alsob takes into consideration the weight of the fuel which varies with each flight time, weather conditions and temperature. But for mt project I must only consider the tip prevention index when planning the payload...

Index -60 is just a optimum loading figure and when the captain works out his fuel (this also uses a matrix to get the fuel index) the index is added to the payload index which is then measured on a special chart called the envelope to see if the flight will fly in trim.

The number of pallets never exceeds the 35 as the priority loading list is given to operations considering the top 35 must ride pallets. We can of course be less pallets.

The centre of the aircraft is indeed the strongest point and there are zone limits as we go through the aircraft, but that is not a issue for us to consider, just to find the very best combination to return an index close to -61.

Thanks for any help.

Cheers Andy

[ANDY]

:yes Hi mdmackillop,

FINALLY THE PENNY DROPPED...

I had not noticed that the weights on column a where no longer the reference point for calculating the matrix but were actually the examples of the pallets. Fantastic....thanks very much indeed.

Any chance you could let me know the code to run the random sort until it reaches the closest conbination of figures to reach -60?

Thanks again, I am in awe!

Cheers Andy

[unmarkedhelicopter]

I'm not going to work on anything I don't understand so you 'may' have some explanations to do.
Each Pallet has a Mass, and this is given in kg ?
There are 36 possible pallet positions but position AR is ALWAYS filled by a ballast pallet with a Mass of 4050 kg What is this ? Why is this so ?
Despite reality, we can ALWAYS assume that the pallets ALL have a CofG exactly in the centre of the pallet.
The overall load balance point is both the centre of lift and the centre of static balance (ie where you want the weight to stand (between the wheels) whilst on the ground)
From the locations, you gave, I assume that the pallets are stored 2 wide by 18 long and 2 wide by 5 long for the 2 load areas ?
Thus I would assume that you would like to have some lateral ballance aswell ? What are the limits on this ?
Given ALL your stated pallet positions and index's I can only count 26 pallet positions, why is that ?
I assume that pallets are loaded from near the CofG such that virtually ANY pallet layout can be acheived assuming sensible loading order. Rather than (say) from the back where the first pallet would tip the plane (Anyone see The Italian Job ? the first one that is !)
As with a see-saw if you place weights at various locations the weight * the distance from the pivot determine the turning moment so each pallet position has a turning index, those at the front negative, those at the back positive.
You wish to achieve a value of -60 is this 60 kg * 1 m ??? what are the dimensions of this index ?
Looking at your table I see no reason why I should not have taken row 7 instead of row 25 for the same pallet and achieved an index of -18.76 instead of -16.415 Why is it 7000 instead of 125 ? What are 7000 and 125 ? What units ? What do they relate to ?
What is a full list of pallet positions and what are the turning factors for each position ?
Once done I assume you merely add each product of mass and turning factor and you want this to equate (ideally) to -60 With what limits ? What is your tolerance ?

Assuming that we actually process each permutaion with 35 pallets in 35 positions we have 1x10^40 possibilities, assuming we do 5000 calculations as second that would take 2.0666x10^36 seconds or 3.44x10^34 minutes or 5.7x10^32 hours or 2.39x10^31 days or 6.55x10^28 years

Okay, so if we do 100,000 calculations per second that still comes out at 3.27x10^27 years ... you can see the possible benefits of using md's random approach and that's before we consider the possibility of discarding pallets to achieve a closer match. And remember that for each calculation here we have to get the next layout permutation (35 evaluations) + get 36 turning factors, multiply by 36 masses, sum the resultants then add the left side and subtract the right side (18 add's and 18 sub's) to get you a lateral result. The sun would go cold and you would be very long retired.

[mdmackillop]

This is a work in progress!
I'm returning -60, but the individual limits are being exceeded. The heavy items have to be restricted in their possible locations.
Inspiration welcome.

[ANDY]

WOW!

That is a lot of questions...

1. Each pallets starts life at 125kg (tare weight of the pallet) then we add the cargo to reach the gross (the only weight we are interested in.

2. There are other special pallets designed for heavy and long loads but we leave this for another project as the variations are very complex.

3. Each built pallet has (in theory) the correct structural capability to cope with the weight loaded and the various positions on the aircraft have a structural limit based on this fact so as long as we do not exceed the max allowed weight in a position but...if the maximum allowed in BL (B-left side) 4050kg and the max allowed in BR (B-right side) is 4050kg it does not follow that the total allowed for BL+BR is 8100kg, somtimes it is less. Then in certain position on the lower deck the pallet weight are added to the maindeck zonal weight with another limitation.

4. The maindeck is configured with 12 pallets side by side then in the tail where it narrows we have single line positions P and R.

4a. The ballast pallet in AR is there because the aicraft is a converted passenger aircraft and a very heavy main deck door was fitted to the rear of the plane. This act as a natural counterbalance and in fact we pre enter an index of -23 here (even though is is not according to the chart)because this what the engineer require.

5. I only showed the main deck config that is why you see only 26 position...the lower deck is configured in single lines and numbered according to the compartment numbers. and there is a different limit for pallets with different widths.

6. Lateral control is an issue but quite easy to control as the limit is quite high and can be covered with an "if" sort of error message as it very rarely happens.

7. CofG on a pallets has no effect, only the positions on the aircraft.

8. There is also a requirement for loading sequence which controls the tip element. This means the Front Lower Deck is loaded first then the Maindeck starting with A then B etc then the rear lower deck. Reverse order for the offloading...

9. If you look at sheet1 you see the weights in column A are only reference point from a paper chart to help the ops guys get a quick find (estimate index) to plan the flight. Our friend mdmackillop changed these to look like 28 actual weights to test the random checker on sheet3. Try deleting them all and put in some fictious numbers then follow his instructions he wrote earlier.

10. Full loading list is just a paper list with the numbers and weights of the pallets plus any extra data.

Phew...I hope that covers all the questions. thanks for taking an interest.

Ciao Andy

[ANDY]

Hi mdmackillop,

I did not realise you and unmarkedheliopter would take so much time and effort...

Many thanks - Andy

[unmarkedhelicopter]

1. Each pallets starts life at 125kg (tare weight of the pallet) then we add the cargo to reach the gross (the only weight we are interested in.So why do the weights start at 80 i.e. less than 125 kg ? and 125 + 2345 = 2470, so where does 7000 come from ? You STILL have not explained this so I can get a handle on it. Why are the pallets not listed just by their gross weight ? are all pallet locations interchangeable ? i.e. does the pallet that goes in AR fit in the space for GL ? and what about P and R ?


3. Each built pallet has (in theory) the correct structural capability to cope with the weight loaded and the various positions on the aircraft have a structural limit based on this fact so as long as we do not exceed the max allowed weight in a position but...if the maximum allowed in BL (B-left side) 4050kg and the max allowed in BR (B-right side) is 4050kg it does not follow that the total allowed for BL+BR is 8100kg, somtimes it is less. Then in certain position on the lower deck the pallet weight are added to the maindeck zonal weight with another limitation. So as md says even assuming we come up with a perfect -60 every time the result 'may' be useless as we have not taken into account these other limitations ?


4a. The ballast pallet in AR is there because the aicraft is a converted passenger aircraft and a very heavy main deck door was fitted to the rear of the plane. This act as a natural counterbalance and in fact we pre enter an index of -23 here (even though is is not according to the chart)because this what the engineer require. so the figure of 4050 kg for AR is irrelevant, we just need to know it contributes -23 to the overall balance ? And just what does -23 mean ?


5. I only showed the main deck config that is why you see only 26 position...the lower deck is configured in single lines and numbered according to the compartment numbers. and there is a different limit for pallets with different widths.So not all pallets can go anywhere ? With different widths some pallets are only able to go in certain locations (another consideration we didn't know about) does this imply that ALL pallets are ballasted (due to their different construction and sizes) such that they ALL mass 125 kg ? (doesn't seem right to me why pay to haul weight you don't need ?) Also this implies loading specific pallets with specific loads prior to calculating the load balance, which 'may' not be in the best configuration.


6. Lateral control is an issue but quite easy to control as the limit is quite high and can be covered with an "if" sort of error message as it very rarely happens.So you are happy to resolve any issues on this manually ? Even though this 'may' be a show stopper ?


7. CofG on a pallets has no effect, only the positions on the aircraft.Beg to differ but if ALL pallets had their CofG towards the back I bet your plane could tip over, I assume you just mean that in general they average out so it is never a consideration.


9. If you look at sheet1 you see the weights in column A are only reference point from a paper chart to help the ops guys get a quick find (estimate index) to plan the flight. Our friend mdmackillop changed these to look like 28 actual weights to test the random checker on sheet3. Try deleting them all and put in some fictious numbers then follow his instructions he wrote earlier.No, I'm still trying to understand the problem, I'm nowhere near looking at a solution. So if I have a pallet of 2345 kg why does it get it's index from the 7000 row ? What if if weighed 1225 kg ? Why do you have a gross weight, divide by some arbitrary number and multiply by your index to get a turning moment ? Why are they not Gross weights and distances ?


Phew...I hope that covers all the questions.No ... it doesn't ... you seem to be avoiding some questions and obfuscating others. If you don't know the answer say so and/or wait till you can ask someone who does know. e.g. fore/aft tolerances, table explanation, turning moment equivalences, lateral balance tolerance, what the indexes actually represent, etc.

I don't even know if you only have 21 pallets if you still fly with a complement of empty ones to make a total of 35 !

Any calculation would be highly specific to a particular aircraft and the modifications done to it. Such being the case I would have thought that one of the selling points of any aircraft would be a program that assisted with just this activity from either the manufacturer or the refitter.

To be honest it seems the best way would be to start with a loading pattern and decide next position by descending weight and balance bias attained so far. A lot of problems could be addressed by putting contingency fuel in a specific (non-symetric) location.

This is a huge problem and I wish md well in solving it for you as I just can't swim in treacle !

[mdmackillop]

Here's my best guess. The weights should be in descending order. Random sorting is done within categories (shown coloured)

[ANDY]

Really Great Job!!!

Many Thanks...it's just what I needed and I can now move on.

Cheers Andy

[ANDY]

Hi There,

I think in all honesty this went too far as I only really needed to have a means to find -60. I do thank you for your interest and help but please be aware that after 31 years of trimming all types of airecraft I do have all the answer...I simply could not explain the dynamics and limitations of my industry in a few exchanges of emails. A basic weight and balance course takes 1 - 2 weeks so you can imagine the amount of data and variations we have to deal with... MTOW, DOW, DOI, TOW, etc etc the list is endless, so again a very big thank you...now I must go very slowly (and carefully) to build from the foundations I now have.

Cheers Andy

[ANDY]

Hi mdmackillop/unmarkedhelicopter and all,

md you have done me a great favour and I am well on my way to getting a resolve...

But I have another limitation which is a real puzzle and wonder if anyone can find the answer.

In order to understand the problem I attach a simple rough layout of how the main deck of the aircraft looks (plan view).

You will see that the door is at the rear and most positions/pallets are loaded side by side and can not be shifted side ways (left to right or right to left) for loading/unloading unless they are moved to the door area. The problem I pose is the fact that we have flights going to more than one airport (e.g. route - starts at station A to station B then to station C then back to station A) so, to avoid offloading and then reloading the pallets for station C in station B we need to keep the offloading pallets for station B on the Left side (door side) and if there are more than 14 pallets for station B (including positions P and R) we need to ensure all pallets for station C are loaded forward of any pallets for station B on the right hand side.

You will see that positions P and R are transverse loads (because of the shape of the aircraft and, like a few othe positions need to be reserved for pallets specially built for these position...so another variation/limitation could be (if possible) to "reserve" positions for certain pallets/weights.

Hope someone has an answer for this puzzle...I can live without it but with it I have the best possible resolve!

Thanks in advance to all and special thanks to mdmackillop.

Cheers Andy

[mdmackillop]

Hi Andy
If you look at the formula in column AC, youll see the formula in the style +Rand()*10 + 100
By changing the final figure in steps of 10, you can create "groups" within which switching can take place. eg BR, CR, DR & ER. If you assign the appropriate pallet weights to these letters, these will be randomized within the process, but within that group only. This will override the descending weight order, which was created to allocate simple weight groups.

[mdmackillop]

BTW, if this works for you, a simple group numbering system could be added to the sheet which the code can incorporate.

[ANDY]

A very good morning to you md,

Well I tried my best but unfortunately my best is not good enough...

I have posted a slightly revised version of you last file and have added some different weights together with the destinations they would come off at (these are listed in col AN). in col AQ I put a formula to increase the last part of the RAND in col AC, then listed the appropriate positions in col AD starting with AL, BL, CL down to ML plus P and R then also LR and MR so that these positions were in line with all the BOM weights. Then I continued to list the remaining positions to be in line with DEL destination. I also some conditional formating on the col AD to quickly see if Left or Right sides had been selected but unfortunately I have failed badly.

The scenario requires all BOM destination weights/pallets (16 in total) to be loaded in AL thru ML plus P, R LR and MR...then AR thru KR are left for DEL destination.

Is this possible?

Cheers Andy