MS Excel Help
Mar 19, 2006 | 6:35 pm
#16
Join Date: Sep 1999
Location: Silicon Valley
Programs: AA:PLT&3MM, HGP:DIA, SPG:GOLD
Posts: 1,896
Looks like there are a few Excel experts here so I might as well ask my own Excel question.
Is it possible to evaluate the contents of a cell and if it's <A the font color should be blue while if it's >A the font color should be green? TIA.
Is it possible to evaluate the contents of a cell and if it's <A the font color should be blue while if it's >A the font color should be green? TIA.
Mar 19, 2006 | 7:08 pm
#17
Join Date: Jan 2006
Location: Los Angeles
Programs: UA, AA
Posts: 4,039
Originally Posted by bp888
Looks like there are a few Excel experts here so I might as well ask my own Excel question.
Is it possible to evaluate the contents of a cell and if it's <A the font color should be blue while if it's >A the font color should be green? TIA.
Is it possible to evaluate the contents of a cell and if it's <A the font color should be blue while if it's >A the font color should be green? TIA.
Mar 19, 2006 | 9:39 pm
#18
FlyerTalk Evangelist
Join Date: May 1998
Location: Massachusetts, USA; AA 2.996MM & Plat Pro, DL 1MM, GM & Flying Colonel
Posts: 25,037
First, format the cell as blue. Then:
From there:
- In the first pull-down box, leave the default "Cell value is" alone
- In the second one, select "Greater than"
- In the fill-in field, enter the value of A (can be another cell reference)
- Click the "Format..." button
- Make the text color green
- Click OK twice: once for the format, once for the conditional formatting
BTW, your conditions left out the value=A case. The above will leave it blue. If you want it green, select "Greater than or equal to" in the second step. If you want to treat it separately, say purple, click the "Add>>" button after you finish these steps to specify another condition. You ought to be able to handle what happens next yourself.
Originally Posted by rar indeed
Format -> Conditional Formatting
- In the first pull-down box, leave the default "Cell value is" alone
- In the second one, select "Greater than"
- In the fill-in field, enter the value of A (can be another cell reference)
- Click the "Format..." button
- Make the text color green
- Click OK twice: once for the format, once for the conditional formatting
BTW, your conditions left out the value=A case. The above will leave it blue. If you want it green, select "Greater than or equal to" in the second step. If you want to treat it separately, say purple, click the "Add>>" button after you finish these steps to specify another condition. You ought to be able to handle what happens next yourself.
May 23, 2006 | 6:48 pm
#19
Original Poster
FlyerTalk Evangelist
Join Date: Jun 2005
Location: QLA
Programs: SBUX Gold
Posts: 14,508
Wow.. resurecting an old but useful thread with a new question.
Let's say I have % grades in Col A, how would I make B display A/B/C/D/F depending on the percentage?
So far, I have =if(A1>90,"A",???). I seemingly can't nest another IF argument so that I can say, "If A1>80, then B, but if not then if A1>70, then C...." and so on. I'm overthinking this, aren't I?
Let's say I have % grades in Col A, how would I make B display A/B/C/D/F depending on the percentage?
So far, I have =if(A1>90,"A",???). I seemingly can't nest another IF argument so that I can say, "If A1>80, then B, but if not then if A1>70, then C...." and so on. I'm overthinking this, aren't I?
May 23, 2006 | 6:55 pm
#22
Join Date: Aug 2005
Location: Orlando, FL, home of the biggest human trap built by a mouse!
Posts: 62
Originally Posted by IceTrojan
Yeesh, then what am I doing wrong? Here's my formula:
=IF(F2>90,"A",(=IF(F2>80,"B",(=IF(F2>70,"C",(=IF(F 2>60,"D","F"))))))
(My % scores are in F2).
=IF(F2>90,"A",(=IF(F2>80,"B",(=IF(F2>70,"C",(=IF(F 2>60,"D","F"))))))
(My % scores are in F2).
=IF(F2>90,"A",if(F2>80,"B",if(F2>70,"C",if(F2>60"D ","F"))))))
May 23, 2006 | 6:57 pm
#24
Moderator, Hilton Honors
Join Date: Nov 2003
Location: on a short leash
Programs: some
Posts: 71,445
Set up a table like this (say in cells A1 to B5) - substitute your own numbers for the grades (maximum number for each grade and should be sorted worst to best).
Say your score is in column A from row # 8 down.
Then to get a grade use the following forumula
"=index($b$1:$b$5,match(a8,$a1:$a5,1))"
Works much better than nested IF statements, especially when your table has hundreds or thousands of entries
Code:
30 F 50 D 60 C 70 B 90 A
Then to get a grade use the following forumula
"=index($b$1:$b$5,match(a8,$a1:$a5,1))"
Works much better than nested IF statements, especially when your table has hundreds or thousands of entries
May 23, 2006 | 7:11 pm
#26
Original Poster
FlyerTalk Evangelist
Join Date: Jun 2005
Location: QLA
Programs: SBUX Gold
Posts: 14,508
Originally Posted by Kiwi Flyer
Set up a table like this (say in cells A1 to B5) - substitute your own numbers for the grades (maximum number for each grade and should be sorted worst to best).
Say your score is in column A from row # 8 down.
Then to get a grade use the following forumula
"=index($b$1:$b$5,match(a8,$a1:$a5,1))"
Works much better than nested IF statements, especially when your table has hundreds or thousands of entries
Code:
30 F 50 D 60 C 70 B 90 A
Then to get a grade use the following forumula
"=index($b$1:$b$5,match(a8,$a1:$a5,1))"
Works much better than nested IF statements, especially when your table has hundreds or thousands of entries
Originally Posted by Invero
Ooooh... that is good. That will totally help out my other spreadsheet. I knew there had to be a better way!! 
May 23, 2006 | 7:18 pm
#27
Join Date: Aug 2005
Location: Orlando, FL, home of the biggest human trap built by a mouse!
Posts: 62
Here's a question for you...
That previous code for matching will come in handy for sure... but I want to take it up one notch.
I have a spreadsheet with client names. Next to the client names are bill codes. Hidden on another sheet is a list of 100 bill codes, and what they mean. I want to be able to put a bill code in next to the client name, and then at the very end of the spreadsheet, have a list of the descriptions of all the applicable bill codes.
For example:
1 - Bill Smith -- ZGR01
2 - Joe Schmoe -- ZGRRV
3 - Sandy Dandy -- GNADA
======================
ZGR01 - Everything
ZGRRV - Rm/Tx/VT
GNADA - Nothing
Is that possible?
That previous code for matching will come in handy for sure... but I want to take it up one notch.
I have a spreadsheet with client names. Next to the client names are bill codes. Hidden on another sheet is a list of 100 bill codes, and what they mean. I want to be able to put a bill code in next to the client name, and then at the very end of the spreadsheet, have a list of the descriptions of all the applicable bill codes.
For example:
1 - Bill Smith -- ZGR01
2 - Joe Schmoe -- ZGRRV
3 - Sandy Dandy -- GNADA
======================
ZGR01 - Everything
ZGRRV - Rm/Tx/VT
GNADA - Nothing
Is that possible?
May 23, 2006 | 7:37 pm
#28
Moderator, Hilton Honors
Join Date: Nov 2003
Location: on a short leash
Programs: some
Posts: 71,445
Yup. In this case your table of descriptions doesnt need to be sorted, but make sure your code entries match exactly. Then inside the "match(lookup,table,#)" put 0 (instead of the 1 above). This finds exact match.
I usually like to put into the formula some code in case there is no exact match (eg typos or new codes). Something like this would do it.
=if(iserror(match(a8,$a$1:$a$5,0)),"Error - no entry",index($b$1:$b$5,match(a8,$a$1:$a$5,0)))
I usually like to put into the formula some code in case there is no exact match (eg typos or new codes). Something like this would do it.
=if(iserror(match(a8,$a$1:$a$5,0)),"Error - no entry",index($b$1:$b$5,match(a8,$a$1:$a$5,0)))
May 23, 2006 | 7:43 pm
#29
Join Date: Jan 2005
Location: Pennsylvania
Programs: AA Platinum Pro, AC *S, Marriott Gold Elite, Hyatt Explorist
Posts: 9,791
I've got 2 perplexing questions
1) can I justify a cell fully so that the text stretches across it?
Example:
|h a p p y| rather than |happy | where | and | are cell boundaries.
2) If I have a group of cells, and I want to copy them somewhere else on the sheet if a certain condition is met, is this possible?
Suppose I have 3 cells. In the first cell is "A" in the second cell (beneath the first cell) is "B" and in the third cell (next to the first cell) has a number.
If that number is greater than 100, I want to copy all 3 cells to a new place.
Thanks
1) can I justify a cell fully so that the text stretches across it?
Example:
|h a p p y| rather than |happy | where | and | are cell boundaries.
2) If I have a group of cells, and I want to copy them somewhere else on the sheet if a certain condition is met, is this possible?
Suppose I have 3 cells. In the first cell is "A" in the second cell (beneath the first cell) is "B" and in the third cell (next to the first cell) has a number.
If that number is greater than 100, I want to copy all 3 cells to a new place.
Thanks
May 23, 2006 | 8:09 pm
#30
FlyerTalk Evangelist
Join Date: May 1998
Location: Massachusetts, USA; AA 2.996MM & Plat Pro, DL 1MM, GM & Flying Colonel
Posts: 25,037
1) I don't know of any way to do this.
2) Enter formulas like these into the two conditional destination cells:
=if(third cell > 100,first cell,"")
=if(third cell > 100,second cell,"")
The "" puts a null text string (i.e., nothing at all) in the destination cells if the condition in the IF function is not satisfied.
2) Enter formulas like these into the two conditional destination cells:
=if(third cell > 100,first cell,"")
=if(third cell > 100,second cell,"")
The "" puts a null text string (i.e., nothing at all) in the destination cells if the condition in the IF function is not satisfied.