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.

Format -> Conditional Formatting

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.

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?

Actually you can nest IF statements... up to 8.

I'm sure there is a better way, but I do not know of one.

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

Try this:
=IF(F2>90,"A",if(F2>80,"B",if(F2>70,"C",if(F2>60"D ","F"))))))

Success! Many thanks ^

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 ;)

Ooooh... that is good. That will totally help out my other spreadsheet. I knew there had to be a better way!! :)

Ok, now you got all advanced on me! I'll have to play around with that, thanks!



Would this be for a mileage tracking spreadsheet? :D

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?

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'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) 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.