futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### Is This Possible In Excel?

Hey everyone, I am creating a custom Weight and Balance spreadsheat for an aircraft at my flight school. I have it all set up so I have to enter the weights and fuel amounts in gallons, and it will give me the takeoff and landing weight. I would like to make it, so if the weight is to heavy to takeoff, a cell of on the right hand side becomes red and says "Exceeding Max Takeoff Weight", but if it is under the max weight that cell wouldn't be red and a different cell would be Green, saying "Have a good flight." Basically I want to change a cell's colors depending if the numbers on a different cell are between a certain range. Is this possible? Thanks!

-Sam
The Pilot is the highest form of life on Earth!

AeroWesty
Posts: 19551
Joined: Sat Oct 30, 2004 7:37 am

### RE: Is This Possible In Excel?

It's called "Conditional Formatting" and you can do it this way:

http://www.techonthenet.com/excel/questions/cond_format1.php
International Homo of Mystery

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

 Quoting AeroWesty (Reply 1):It's called "Conditional Formatting"

I tried that, but it only colors the cell that contains the number... not a different cell. I have it set up so if Cell B9 is between 0 and 2300, I want cell E6 to change color, not B9. Know what I mean?

-Sam
The Pilot is the highest form of life on Earth!

AeroWesty
Posts: 19551
Joined: Sat Oct 30, 2004 7:37 am

### RE: Is This Possible In Excel?

Right, you have to apply the conditional formatting to cell E6, that would be linked to B9 to display a result. Have you used formulas before?
International Homo of Mystery

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

But how do I tell it that I want to change the color on E6 and not B9? I don't want to display anything in E6 except for the "Have a good flight." I have applied the conditional formatting to E6, but it doesn't do anything because "Have a good flight" isn't between 0 and 2300. If that made any sense...

I have used formulas alot, yep.

-Sam
The Pilot is the highest form of life on Earth!

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

 Quoting AeroWesty (Reply 3):Right, you have to apply the conditional formatting to cell E6, that would be linked to B9 to display a result. Have you used formulas before?

I use Excel extensively and what FutureUSPilot is talking about is a real pain!!! He is right in that conditional formating can only be applied to the cell itself and not referenced to another cell. (Try it AeroWesty, you will notice that the condition drop down box only contains two options. One say "Cell Value is" and the other says "Formula is"

If anyone knows the answer, I will be a happy man!!
If at first you don't succeed, skydiving is not for you

AeroWesty
Posts: 19551
Joined: Sat Oct 30, 2004 7:37 am

### RE: Is This Possible In Excel?

Hrm, well I'm not at home with all my Excel stuff, and won't be for another week or so, but it should be able to be done like this:

1) B9 will equal a value. Make E6 equal the value of B9, thereby qualifying for conditional formatting, then

2) If B9 is above a value, it displays "message", if below a value, it displays "a different message", then

3) The conditional formatting of E6 will be on the value it equals, not the message it displays

Someone else might have an easier way, this is just off the top of my head having done a few things similar in the past, but without the spreadsheets to refer to in front of me.
International Homo of Mystery

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

I know what your trying to say, but Where do we enter in the different messages we want displayed? It doesn't give you a place to do that in any of the conditional formatting sections. I bet it would be alot easer to have Excel in front of you!

 Quoting Yhmfan (Reply 5):If anyone knows the answer, I will be a happy man!!

If anyone knows the answer, I will be a happy man!![/quote]

That would make two of us! By the way it's UA there buddy... not US.

-Sam
The Pilot is the highest form of life on Earth!

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

Hey Sam;
I think I just had a moment of brilliance... doesn't happen very often

Let us assume cell D6 has the value and you want cell E6 to say "Have a good flight" in green and cell F6 to say "Maximum Weight Exceeded" in red.

In cell E6 type in the following:

=IF(D6<2300,"Have a Good Flight!","")
Now format the text in green.

In cell F6 type in the following:
=IF(D6<2300,"","Maximum Weight Exceeded!")
Now format the text in red.

Good luck!!
If at first you don't succeed, skydiving is not for you

AeroWesty
Posts: 19551
Joined: Sat Oct 30, 2004 7:37 am

### RE: Is This Possible In Excel?

 Quoting FutureUApilot (Reply 7):Where do we enter in the different messages we want displayed?

Like YHM said, and I didn't say too clearly, the text has to be part of a formula, such as he gave an example of:

 Quoting Yhmfan (Reply 8):=IF(D6<2300,"Have a Good Flight!","")
International Homo of Mystery

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

 Quoting Yhmfan (Reply 8):I think I just had a moment of brilliance... doesn't happen very often

HAHA! Sometime people don't need many moments of brilliance! Let me try it out and see what happens!

-Sam
The Pilot is the highest form of life on Earth!

searpqx
Posts: 4173
Joined: Thu Jun 29, 2000 10:36 am

### RE: Is This Possible In Excel?

I think what you're trying to do is this
Formula in B9:
=Sum(B8+B7) (or whatever)

Formula in E6:
=IF(B9>2500,"Exceeds Max Take Off Weight","")

Then, put a conditional format on E6 to format background red when
Cell Value Is, Equal To, Exceeds Max Take Off Weight

Now whenever B9 > 2500 The text "Exceeds Max Take Off Weight" will appear in E6, and the background will be red.

If that doesn't work, drop me a line - I teach Excel, so I'm sure we can work it out.
"The two most common elements in the universe are Hydrogen and stupidity"

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

HEY! It now will say "Have a good fight if that landing and takeoff weight is below 2300, but if it is above it says FALSE. Is there any way to make it just disappear? Thanks so much for all the help!!!

-Sam
The Pilot is the highest form of life on Earth!

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

 Quoting FutureUApilot (Reply 12):HEY! It now will say "Have a good fight if that landing and takeoff weight is below 2300, but if it is above it says FALSE

Hi Sam;
I suspect you missed the second blank ""
i.e you have

=IF(D6<2300,"Have a Good Flight!")

=IF(D6<2300,"Have a Good Flight!","")
If at first you don't succeed, skydiving is not for you

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

 Quoting Yhmfan (Reply 13):I suspect you missed the second blank ""

Haha, yep, that was it! Now, I know i'm asking for alot here, but can we make it so the "Have a good flight" will be green and the "Exceeds Max Takeoff Weight" will be red? This is so cool!!!!

-Sam
The Pilot is the highest form of life on Earth!

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

 Quoting FutureUApilot (Reply 14):but can we make it so the "Have a good flight" will be green and the "Exceeds Max Takeoff Weight" will be red?

No problem!
You simply format cell E6 to have green text and format cell F6 to have red text. To change the text colour click on the "Font Text" botton or from the menu "Format", "Cell" and then click on the font tab and about half way down is the colour.
Keep in mind that, when they are blank, the colour does not show.
If at first you don't succeed, skydiving is not for you

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

What if I wanted to combine the "Have a good flight" and "Exceeding Max Takeoff Weight" into one cell, I set it up like this:

=IF(B22<2500,"Takeoff Weight Within Limits","Excedes Max Takeoff Weight")

Can we make it so "Exceedes Max Takeoff Weight" is red while "Takeoff Weight Within Limits" is green? Maybe i'm asking for too much... I'm off to bed now, so I'll see the replies in the morning. Again, thanks for all of your guy's and gal's help so far! I know I will have some more questions tomorrow, so i'll be posting here again tomorrow evening. Thanks everyone!

-Sam
The Pilot is the highest form of life on Earth!

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

Now you can use conditional formating to change colours in cell, I am assuming, C22.

Basically go to conditional formating and state that if the cell value equals "Takeoff Weight Within Limits" then the format is green and if it is "Exceedes Max Takeoff Weight" then it is red.

[Edited 2006-01-29 06:06:55]
If at first you don't succeed, skydiving is not for you

andz
Posts: 7623
Joined: Mon Feb 16, 2004 7:49 pm

### RE: Is This Possible In Excel?

This is easy:

select the cell where the formula is.
click Format, Conditional Formatting
for condition 1, click cell value is.... equal to.... type in have a good flight
click format
click pattern
select green, click OK

repeat above for condition 2, cell value is... equal to... exceeds max takeoff weight
click format
click pattern
select red, click OK

spelling must be right or it won't work!
After Monday and Tuesday even the calendar says WTF...

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

AH! I thought you could only use Conditional Formatting for numbers! Ok! I understand now! Thanks!

-Sam
The Pilot is the highest form of life on Earth!

futureuapilot
Topic Author
Posts: 1329
Joined: Mon May 24, 2004 7:50 am

### RE: Is This Possible In Excel?

I let this project just sit for a while just to get my mind off of it, but now it's time to finish. This is another thing that I couldn't figure out.

This is a picture of the aircraft's (N6401K) center of gravity safety envelope. Is it possible so if the CG is in the envelope we could do that "Have a good flight" or "Aircraft CG is not with in limits"? If it was only one axis I know I could do this, but now we are dealing with an X and Y axis. I'm guessing something with the conditional formatting again, but how do you put two axis's into the equasion? Something like this?

=IF(C24<47 and C24>355 and D24<2400,"Center of Gravity Within Limits","Excedes Center of Gravity Weight")

Is that even close? I am at a complete loss of ideas. Thanks!

-Sam
The Pilot is the highest form of life on Earth!

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

 Quoting FutureUApilot (Reply 20):=IF(C24<47 and C24>355 and D24<2400

Something not quite right here. C24 cannot be less than 47 and greater than 355 at the same time. (I think you may have meant to say 35 not 355)
Does this mean that column C contains The x axis and column D the Y axis?
I am not familiar with the safety envelope and what it means but, looking at the graph, your formula can give the incorrect result. For example x at 37 and y at 2,300 will say "within limits" where, in fact, it is not.
I think you need to use two formulas, one for for CG between 39.5 and 47 and one for CG between 35 and 39.5.
Once you get that part right, the conditional formatting is the easy part.
Let me scratch my head a bit and see if I can think of something.
If at first you don't succeed, skydiving is not for you

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

Based on the graph, here is my suggestion for creating a table that represents the outer limits of the envelope:

Based on your graph, the sloped portion of the envelope has the following approximate formula: Weight - 100 X CG - 1550

In column C type in the values for CG. Use, say, 1/2 inch intervals

In column D type in the following formula (Assume row 3)

=IF(AND(C3>35,C3<39.6),C3*100-1550,IF(AND(C3>39.5,C3<47),2400,0))

Now you have two columns that show the outer edges of the envelope.

I guess (and this depends on how you are trying to present this) the next step is to put the actual weight and the actual CG in two other cells and see if they fall within the nvelope. You can then apply conditional formating for presentation purpuses.

Let me know if this is what you have in mind Sam.

PS... Please note how the "AND" statement works in Excel.
If at first you don't succeed, skydiving is not for you

yhmfan
Posts: 574
Joined: Tue Feb 17, 2004 2:44 pm

### RE: Is This Possible In Excel?

Hmmm.. on second thought, the bit about putting the CG and weight in two cells and seeing if it works is kinda tricky.
It may be clumsy, but probably the best way is to put in the CG and come up with a maximum weight using VLOOKUP function.

To summarise:

1. Starting in cell C3 and ending in cell C31 put in the CG in 1/2 inch intervals from 34 to 48

2. Starting in cell D3 and ending in cell D31 copy the following formula:

=IF(AND(C3>35,C3<39.6),C3*100-1550,IF(AND(C3>39.5,C3<47),2400,0))

3. Name the range C3:D31 as "cg"

4. Type in the actual weight in B1

5. Type in the actual CG in B2

6. In B3 type in the following formula:

=VLOOKUP(B2,cg,2,FALSE)

7. In B4 type in the following formula

=IF(B3>B1,"Center of Gravity Within Limits","Excedes Center of Gravity Weight")

8. Type in the necessary labels in column A.
A1 Actual Weight, A2 Actual CG, A3 Maximum Weight allowed for actual CG

9. Use conditional formatting on B4 to change colours.

Good Luck...... this senior citizen has to go to bed now!!
If at first you don't succeed, skydiving is not for you

SpinalTap
Posts: 390
Joined: Fri Mar 18, 2005 7:18 pm

### RE: Is This Possible In Excel?

There are many possible ways of doing things in Excel and Yhmfan has suggested one legitimate way but this is the way I would do it:

Based on:
Airplane c.g location = cell C24
Loaded airplane weight (actual) = cell D24

This formula will give you the value on the line (limiting airplane weight) from the plot from your airplane c.g location
=IF(C24<35,0,IF(C24<39.5,100*C24-1550,IF(C24<47.3,2400,0)))
(please check this formula against the plot to make sure)

You can then compare the limiting value of the Loaded airplane weight (the line value) with your actual loaded airplane weight by using this IF statement (inlucdes above IF statement "nested")
=IF(D24>IF(C24<35,0,IF(C24<39.5,100*C24-1550,IF(C24<=47.3,2400,0))),"Excedes Center of Gravity Weight", "Center of Gravity Within Limits")

Hope this is what you meant.
"I get what they call a stipend, a stipend is like money but its such as small amount they don't really call it money"

### Who is online

Users browsing this forum: Aesma, alberchico, salttee and 45 guests

### Popular Searches On Airliners.net

Top Photos of Last:   24 Hours  •  48 Hours  •  7 Days  •  30 Days  •  180 Days  •  365 Days  •  All Time

Military Aircraft Every type from fighters to helicopters from air forces around the globe

Classic Airliners Props and jets from the good old days

Flight Decks Views from inside the cockpit

Aircraft Cabins Passenger cabin shots showing seat arrangements as well as cargo aircraft interior

Cargo Aircraft Pictures of great freighter aircraft

Government Aircraft Aircraft flying government officials

Helicopters Our large helicopter section. Both military and civil versions

Blimps / Airships Everything from the Goodyear blimp to the Zeppelin

Night Photos Beautiful shots taken while the sun is below the horizon

Accidents Accident, incident and crash related photos

Air to Air Photos taken by airborne photographers of airborne aircraft

Special Paint Schemes Aircraft painted in beautiful and original liveries

Airport Overviews Airport overviews from the air or ground

Tails and Winglets Tail and Winglet closeups with beautiful airline logos