Change Forum... Civil Aviation Travel, Polls & Prefs Tech/Ops Aviation Hobby Aviation Photography Photography Feedback Trip Reports Military Av & Space Non-Aviation Site Related LIVE Chat My Starred Topics | Profile | New Topic | Forum Index | Help | Search
 Is This Possible In Excel?
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4Posted Sun Jan 29 2006 03:02:34 UTC (10 years 4 months 1 day 4 hours ago) and read 3849 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!
 AeroWesty From United States of America, joined Oct 2004, 20822 posts, RR: 60 Reply 1, posted Sun Jan 29 2006 04:04:34 UTC (10 years 4 months 1 day 3 hours ago) and read 3820 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
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 2, posted Sun Jan 29 2006 04:25:26 UTC (10 years 4 months 1 day 3 hours ago) and read 3811 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!
 AeroWesty From United States of America, joined Oct 2004, 20822 posts, RR: 60 Reply 3, posted Sun Jan 29 2006 04:28:58 UTC (10 years 4 months 1 day 3 hours ago) and read 3810 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
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 4, posted Sun Jan 29 2006 04:34:54 UTC (10 years 4 months 1 day 3 hours ago) and read 3800 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!
 Yhmfan From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 5, posted Sun Jan 29 2006 04:40:01 UTC (10 years 4 months 1 day 2 hours ago) and read 3795 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
 AeroWesty From United States of America, joined Oct 2004, 20822 posts, RR: 60 Reply 6, posted Sun Jan 29 2006 04:43:40 UTC (10 years 4 months 1 day 2 hours ago) and read 3789 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
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 7, posted Sun Jan 29 2006 04:49:22 UTC (10 years 4 months 1 day 2 hours ago) and read 3782 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.

-Sam

 The Pilot is the highest form of life on Earth!
 Yhmfan From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 8, posted Sun Jan 29 2006 04:49:38 UTC (10 years 4 months 1 day 2 hours ago) and read 3782 times:

 Hey Sam; I think I just had a moment of brilliance... doesn't happen very often   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
 AeroWesty From United States of America, joined Oct 2004, 20822 posts, RR: 60 Reply 9, posted Sun Jan 29 2006 04:52:52 UTC (10 years 4 months 1 day 2 hours ago) and read 3775 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
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 10, posted Sun Jan 29 2006 04:53:17 UTC (10 years 4 months 1 day 2 hours ago) and read 3775 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!
 Searpqx From Netherlands, joined Jun 2000, 4349 posts, RR: 9 Reply 11, posted Sun Jan 29 2006 04:55:39 UTC (10 years 4 months 1 day 2 hours ago) and read 3771 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"
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 12, posted Sun Jan 29 2006 05:02:13 UTC (10 years 4 months 1 day 2 hours ago) and read 3766 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!
 Yhmfan From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 13, posted Sun Jan 29 2006 05:06:50 UTC (10 years 4 months 1 day 2 hours ago) and read 3757 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!")

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

 If at first you don't succeed, skydiving is not for you
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 14, posted Sun Jan 29 2006 05:09:41 UTC (10 years 4 months 1 day 2 hours ago) and read 3751 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!
 Yhmfan From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 15, posted Sun Jan 29 2006 05:14:18 UTC (10 years 4 months 1 day 2 hours ago) and read 3746 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
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 16, posted Sun Jan 29 2006 05:24:17 UTC (10 years 4 months 1 day 2 hours ago) and read 3735 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!
 Yhmfan From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 17, posted Sun Jan 29 2006 05:55:52 UTC (10 years 4 months 1 day 1 hour ago) and read 3715 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
 Andz From South Africa, joined Feb 2004, 8594 posts, RR: 9 Reply 18, posted Sun Jan 29 2006 06:02:39 UTC (10 years 4 months 1 day 1 hour ago) and read 3713 times:

 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...
 FutureUApilot From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 19, posted Sun Jan 29 2006 16:46:30 UTC (10 years 4 months 14 hours ago) and read 3668 times:

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 From United States of America, joined May 2004, 1365 posts, RR: 4 Reply 20, posted Mon Jan 30 2006 01:35:55 UTC (10 years 4 months 6 hours ago) and read 3611 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. 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 From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 21, posted Mon Jan 30 2006 03:35:43 UTC (10 years 4 months 4 hours ago) and read 3600 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
 Yhmfan From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 22, posted Mon Jan 30 2006 03:57:13 UTC (10 years 4 months 3 hours ago) and read 3592 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
 Yhmfan From Canada, joined Feb 2004, 608 posts, RR: 0 Reply 23, posted Mon Jan 30 2006 05:03:03 UTC (10 years 4 months 2 hours ago) and read 3581 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!!
 If at first you don't succeed, skydiving is not for you
 SpinalTap From New Zealand, joined Mar 2005, 441 posts, RR: 0 Reply 24, posted Mon Jan 30 2006 06:20:09 UTC (10 years 4 months 1 hour ago) and read 3569 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 Change Forum... Civil Aviation Travel, Polls & Prefs Tech/Ops Aviation Hobby Aviation Photography Photography Feedback Trip Reports Military Av & Space Non-Aviation Site Related LIVE Chat 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