• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help is needed to simplify formula

N

nader assaleh

Member
Hi all
I have written nested if formula with countif function, the problem is that the formula is very tedious and is not tweaked and need to be written again.
the idea is if I choose a certain cell (aircraft position) then other overlapping cells (positions) must not be used (must be blocked). hope the attaching file will show the idea clearly. hope somebody help me.
thanks,
 

Attachments

  • Book11.xlsx
    16.7 KB · Views: 9
Hi ,

First , I do not know why there need to be merged cells ( such as E6 and F6 ) as well as multiple cells ( such as E7 , F7 and G7 )

If you deal with single cells and have their designations within them , whether it is through formulae or through VBA , everything becomes simpler. The fact that one cell can refer to multiple physical compartments is immaterial , as long as one physical compartment cannot be sub-divided.

Secondly , you need to make other members' job easier by describing in words what the logic for blocking is , rather than make it mandatory for them to go through your formula to understand the logic , especially when you yourself think the formula may not be perfect.

Narayan
 
Hi ,
the only reason to make merged cells is to make it simple to understand otherwise there is no reason.
secondly as we are talking about aircraft compartments then it can only be loaded in certain way if we use 31p then 31l,31r,32l,32r must be blocked and
and to restrict user from using them.
 
Hi ,
attached file shows exactly how real excel sheet will be.
column D is where data validation should be applied.
 

Attachments

  • lpad.xlsx
    17.6 KB · Views: 8
Hi,
regarding "The fact that one cell can refer to multiple physical compartments is immaterial , as long as one physical compartment cannot be sub-divided." the actual case states that aircraft compartment can be loaded either right or lift or in the middle ( not exactly). if we load the aircraft in the middle (31p) then right 31r and32r and lift positions 31l and 32r which overlap with 31p must be blocked. hope this make it little bit easier to understand.
help is needed please
 
Hi Nader ,

I am sorry , but I really do not understand anything of your requirement.

1. What is the exact objective ?

2. If it is to do with aircraft loading or some such requirement , how many compartments are there in all ?

3. Why are you always talking of 31l , 32r and so on ? What is the exact numbering scheme , where does it start and where does it end ?

4. What is the pictorial representation of the entire system ?

5. What is the general logic , which will be applied to any and every compartment ?

Since Jeffrey Weir has mentioned that he understands your requirement , I hope he will help you out.

If someone else who has understood you well can explain , I can try and help.

Narayan
 
Hi,
first of all sorry for my poor English langguage. i will try my best to simplify things.
the axcat objection is to confine user not to load aircraft in wrong mannar

most aircrafts have five compartments
cpt 1, cpt 2, cpt3, cpt4 and cpt5.
cpt5 is for bulk loading

cpt1 always start with number 1

11L means cpt1 position 1 lift side
12r means cpt1 position 2 right side

cpt2 always starts with number 2
e.g
23L means cpt2 position 3 lift side (we load AKE or AKH)
24 means cpt2 position 4 (we load ALF or PLA)
21p means cpt2 position 1 pallet position

and so on
the excact numbering depend on aircraft type but all wide-bodied A330,A340, B787 ans so on starts with 11 and end with 5

aircraft are of two types
1- narrow bodied aircrafts in which we can load caintainers one after the other.
in this case there is no problem of loading.
2- wide-bodied aircrafts (e.g A330,A340,B787 and so on)
in this kind of aircrafts we can load various types of containers
1- AKH and AKE (base dimension 60*61) which can be loaded either in lift side ( any number ends with L letter eg. 11L,12L,21L ...etc) or right side (any number ends wit R letter eg 11R,12R,31R....etc) if we have two AKE then the will occupy say 11L and 11R.
2- ALF and PLA (base dimension(125*61) both are double the size of AKH or AKE and we refer them as 11
3- PMC, PAJ and PAG all are called as pallets ( base dimension 125*88) these takes an area more than ALF.
i did try my best to describe things but my English efficiency is not that good.
hope to hear from you soon.
 

Attachments

  • Book11.xlsx
    26.8 KB · Views: 6
Hi Nader ,

Thanks for that explanation ; now things are more clear. However , that is a lot of information to be read and understood / visualized.

Can you check back tomorrow ?

Narayan
 
Hi Nader ,

If your requirement is such that you will be using this regularly , then I think it will be better if we plan this application well , and develop a robust solution , using either formulae or VBA or a combination of both , which allows the user to configure the application as much as possible.

So , let me put down all that I have understood about the application.

1. There are a variety of planes ; this means that we need to have a system by which we can categorize planes so that each type of plane is identified.

You have mentioned 2 types , viz. narrow-bodied and wide-bodied.

You also say that narrow-bodied aircraft pose no problem ; does this mean that this application will not be used for narrow-bodied aircraft ?

Secondly , in the case of wide-bodied planes , does this mean that whether the plane is an A-330 or an A-340 or a B-787 , it does not make any difference to the application or the solution ?

If there is a difference , what is / are the difference(s) ?
................................................................................................................

2. There are a variety of compartments ; again , we need a system of coding these.

The highest level in the hierarchy is the HOLD , of which you mention 3 :

Forward Hold

Aft Hold

Bulk Hold

Are the Forward Hold and the Aft Hold identical ?
................................................................................................................

At the lower level , there are compartments ; the Forward Hold and the Aft Hold have 2 compartments each , while the Bulk Hold has only 1 compartment.

Are the first 4 compartments in the Forward Hold and the Aft Hold identical ?
................................................................................................................

Each compartment is further sectioned into , let us call them areas.

There seem to be 3 types of areas , labelled L , R and P. Are the L and R areas identical ?

The labelling of the areas within the compartments seems to have some issues :

There are two 32R , 33R and 41R areas.
................................................................................................................

Lastly , we have the cargo itself.

You have mentioned 7 types of cargo :

AKH , AKE , ALF , PLA , PMC , PAJ and PAG.

Are these the only types of cargo , or are there more ? If there are , will they also be used in this application ?

Other than the dimension of each type of cargo , is there any other factor which will be of relevance when the cargo is stowed in an area ?
..................................................................................................................

So , now we come to the actual rules for stowage.

I can make out just one rule :

When there is an even number of AKH or AKE cargo , because they can be stowed in any orientation , they will be stowed in rows ; so if there are 2 AKE items of cargo , they will be stowed one in the L area , and the other in the corresponding R area ; does this mean that they cannot be stowed in two L areas or 2 R areas if these are the only ones remaining to be filled up ?

I am putting all this down so that as and when all of it is in writing , anyone else who is interested in developing this application , can also take it up.

Narayan
 
Hi Narayan
this application is going to be used as back up when the main application is down which happens due server probem, power failure, updating the application, internet problem.....etc.
this is the last problem in this application that i have to overcome.
to clear things more let us start from the beginning

aircraft types
1- bulk loading aircrafts in my case E75 and E95 in which we have only two holds
forward hold 1 and aft hold 4.

2- narrow-bodied aircrafts A319, A320, A321. here we only have one container per row say 11,12,13 . here we only can load AKH and/or PKC only.
in narrow bodied aircraft ther is no problem of loading since poisitions do not overlaps


3-wide-bodied aircrafts A330,A340,B787. in this type of aircrafts we can load
two containers (AKH and/or AKE) per row
or
ALF or PLA per row (since they are double the size of AKH or AKE
or
PAJ,PAG,PMC the occupy almost one and half row

secondly all wide bodied aircrafts have same numbering scheme the but since aircrafts are of different size then specific compartment ( say cpt) may differ
you may find 24l, 24r in one type (b787) but 24l,24r is not there in A330.

2- always the hierarchy is as the following

a-forward hold which consists of

cpt 1
cpt 2
b- aft hold which consists of

cpt 3
cpt 4
c- bulk hold
bulk loading only allowed here.

we have L for lift
R for right
21L and 21R are identical and they are parallel to each other.

p is used for pallet loading (PMC,PAJ,PAG)


holds are not identical in different aircraft types.. they may differ


know, we have the following type of unit load device (ULD) containers

1- AKH, PKC can be used for all type of aircrafts but
2- AKE can only be used with wide-bodied aircraft
3- ALF, PLA double size (one row lift and right) can only be used with wide-bodied aircraft
4- PAJ,PAG,PMC these are pallets and more wide than one row. can only be used with wide-bodied aircraft.

these are the only used ULD .

finally no other factor only dimension is considered

if we have two AKE they can be loaded in any way say
1- L and L
2- L and R
3- R and R

i am attaching a file in which you find all aircraft type loading instruction



MANY THANKS
 
Hi Nader ,

Thank you for giving so much of information ; however , in view of the amount of information that needs to be understood , I hope you are not in a hurry.

Please allow for at least a day.

Narayan
 
Hi Nader ,

Can you confirm the following ?

Is this application to consider all 3 types of aircraft ? Or are you restricting it only to one or two types ?

Can we stick to one terminology ? Even from your earlier posts I was under the impression that all aircraft have only 3 holds viz. Forward , Aft and Bulk ; I don't understand the following statement in your latest post :

1- bulk loading aircrafts in my case E75 and E95 in which we have only two holds forward hold 1 and aft hold 4.
Click to expand...
Can you clarify whether bulk loading aircraft have only 2 holds or 3 ?

And what about the wide-bodied and narrow-bodied aircraft ? How many holds do they have ?

Since you say that the numbering scheme for different aircraft is different , can we list down the scheme for each aircraft for which you will consider using this application ?

You also mention that holds are not identical in all aircraft ; can we then list down for each aircraft the different holds , and their capacities in terms of weight and volume ?

The time-consuming part of this application seems to be the conversion of the aircraft images into data that can be processed using either formulae or VBA ; if the data structures are chosen well , the application itself should be straightforward.

What is left to be discussed is the actual data input into the application ; what are the values that will be entered by the user , and what is the expected output from the application ?

Narayan
 
Hi Narayan
Good day
kinldy find the following
1-this application has to consider all aircraft types
2-regarding hold, compartment and numbering scheme
E75 and E95
we have 2 holds
fwd hold and aft hold
compartment and numbering scheme are the same
cpt 1 and cpt 4

A319

3 holds
fwd aft bulk
cpt1 cpt4 cpt4
11,12 41,42 51

A320

3holds

fwd aft bulk
cpt1 cpt3 cpt 4 cpt5
11,12,13 31,32 41,42 51


A321

3 holds

fwd aft bulk
cpt1 cpt2 cpt3 cpt4 cpt5
11,12 21,22,23 32,33 41,42 51

A330

3 holds
fwd
cpt1 cpt2
11l,11r,12l,12r,13l,13r,14l,14r (ake,akh,pkc) 21l,21r,22l,22r,23l,23r
11,12,13,14 (ALF,PLA) 21,22,23
12p,13p (PMC,PAG,PAJ) 21P,22P

aft
cpt3 cpt4
31l,31r,32l,32r,33l,33r 41l,41r,42l,42r,43l,43r
31,32,33 41,42,43
31p,32p 41p,42p

bulk hold
51

A340

3 holds
fwd
cpt1 cpt2
11l,11r,12l,12r,13l,13r (ake,akh,pkc) 21l,21r,22l,22r,23l,23r,24l,24r
11,12,13 (ALF,PLA) 21,22,23,24
11p,12p (PMC,PAG,PAJ) 21P,22P,24p

aft
cpt3 cpt4
31l,31r,32l,32r,33l,33r 41l,41r,42l,42r,43l,43r,44l
31,32,33 41,42,43
31p,32p 41p,42p

bulk hold
51


B787

3 holds
fwd
cpt1 cpt2
11l,11r,12l,12r,13l,13r,14l,14r (ake,akh,pkc) 21l,21r,22l,22r,23l,23r,24l,24r
11,12,13,14 (ALF,PLA) 21,22,23,24
,11p12p,13p (PMC,PAG,PAJ) 21P,22P

aft
cpt3 cpt4
31l,31r,32l,32r,33l,33r 41l,41r,42l,42r,43l,43r
31,32,33 41,42,43
31p,32p 41p,42p

bulk hold
51

regrading wright and volume it is covered in different part of the application

regarding the values to be entered. it be restricted by ULD type ( e.g ake, akh etc). for that the maximum weight can be loaded per ULD type is the following

pkc 1134
akh 1134 kg
ake 1587 kg
alf 3174
pla 3174
paj 4626
pag 4626
pmc 4626

hope this will suffice

many thanks
 
You must log in or register to reply here.
Back
Top