Sponsor Message:
Non Aviation Forum
My Starred Topics | Profile | New Topic | Forum Index | Help | Search 
Is This Possible In Excel?  
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Posted (8 years 6 months 5 days 11 hours ago) and read 2650 times:

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!
24 replies: All unread, jump to last
 
User currently offlineAeroWesty From United States of America, joined Oct 2004, 20394 posts, RR: 62
Reply 1, posted (8 years 6 months 5 days 10 hours ago) and read 2621 times:

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
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 2, posted (8 years 6 months 5 days 10 hours ago) and read 2612 times:

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!
User currently offlineAeroWesty From United States of America, joined Oct 2004, 20394 posts, RR: 62
Reply 3, posted (8 years 6 months 5 days 10 hours ago) and read 2611 times:

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
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 4, posted (8 years 6 months 5 days 10 hours ago) and read 2601 times:

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!
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 5, posted (8 years 6 months 5 days 10 hours ago) and read 2596 times:

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
User currently offlineAeroWesty From United States of America, joined Oct 2004, 20394 posts, RR: 62
Reply 6, posted (8 years 6 months 5 days 10 hours ago) and read 2590 times:

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
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 7, posted (8 years 6 months 5 days 9 hours ago) and read 2583 times:

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.  Cool

-Sam



The Pilot is the highest form of life on Earth!
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 8, posted (8 years 6 months 5 days 9 hours ago) and read 2583 times:

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

How about this:


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
User currently offlineAeroWesty From United States of America, joined Oct 2004, 20394 posts, RR: 62
Reply 9, posted (8 years 6 months 5 days 9 hours ago) and read 2576 times:

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
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 10, posted (8 years 6 months 5 days 9 hours ago) and read 2576 times:

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!
User currently offlineSearpqx From Netherlands, joined Jun 2000, 4343 posts, RR: 10
Reply 11, posted (8 years 6 months 5 days 9 hours ago) and read 2572 times:

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"
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 12, posted (8 years 6 months 5 days 9 hours ago) and read 2567 times:

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!
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 13, posted (8 years 6 months 5 days 9 hours ago) and read 2558 times:

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!")

instead of

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



If at first you don't succeed, skydiving is not for you
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 14, posted (8 years 6 months 5 days 9 hours ago) and read 2552 times:

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!
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 15, posted (8 years 6 months 5 days 9 hours ago) and read 2547 times:

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
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 16, posted (8 years 6 months 5 days 9 hours ago) and read 2536 times:

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!
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 17, posted (8 years 6 months 5 days 8 hours ago) and read 2516 times:

Your formula is correct Sam.
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
User currently offlineAndz From South Africa, joined Feb 2004, 8443 posts, RR: 10
Reply 18, posted (8 years 6 months 5 days 8 hours ago) and read 2514 times:
Support Airliners.net - become a First Class Member!

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

click add
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...
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 19, posted (8 years 6 months 4 days 22 hours ago) and read 2469 times:

Quoting Yhmfan (Reply 17):
Your formula is correct Sam.

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!
User currently offlineFutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4
Reply 20, posted (8 years 6 months 4 days 13 hours ago) and read 2412 times:

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.

http://img.photobucket.com/albums/v284/pilotboy523/scan001001New.jpg

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!
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 21, posted (8 years 6 months 4 days 11 hours ago) and read 2401 times:

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
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 22, posted (8 years 6 months 4 days 10 hours ago) and read 2393 times:

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
User currently offlineYhmfan From Canada, joined Feb 2004, 607 posts, RR: 0
Reply 23, posted (8 years 6 months 4 days 9 hours ago) and read 2382 times:

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!!  yawn 



If at first you don't succeed, skydiving is not for you
User currently offlineSpinalTap From New Zealand, joined Mar 2005, 440 posts, RR: 0
Reply 24, posted (8 years 6 months 4 days 8 hours ago) and read 2370 times:

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"
Top Of Page
Forum Index

This topic is archived and can not be replied to any more.

Printer friendly format

Similar topics:More similar topics...
OK Guys, What Is This Thing In The Sky? posted Tue Oct 3 2006 22:56:27 by Thom@s
Infinite Frequent Flyer Miles - Is This Possible? posted Tue Jun 20 2006 19:09:57 by Birdwatching
Is This A Ghost In This Cabin Photo? posted Tue Apr 25 2006 17:18:21 by Mattlad
Is It Really This Strict In Dubai? posted Wed Apr 27 2005 19:45:03 by KLMA330
Is This A 2 Pointer Or 3 Pointer In Basketball? posted Mon Jan 24 2005 13:33:49 by GKirk
Where In The Alps Is This? posted Sat Jan 1 2005 17:49:59 by Caribb
Is It Really This Bad In Iraq? 8 US Dead Today? posted Fri Sep 19 2003 01:57:37 by Cedarjet
Is This Even Possible? posted Tue Nov 19 2002 20:51:01 by Wn700driver
Is This The Longest Word In The World... posted Mon Apr 29 2002 20:13:05 by Ammunition
What Is It That There Is More Of In This World.... posted Mon Mar 18 2002 12:51:18 by AMSMAN