Alphabet sequencing in a conditional format

C

craftwoodman

I have a problem that I need to get resolved as soon as possible and need
help. I have created a tracking sheet for some stock investments I have.
The web site I use for information is a pay site that uses alpha and numeric
characters to show strength and weakness. I have no problem with the number
part of this, but the alpha part is driving me crazy. I need to have the
alpha characters in the following sequence of order. A+, A, A-, B+, B, B-,
C+, C, C-, etc...., but when I use the same =AND formula that I use for the
numbers the sequence comes out like this. A+, A-, A, B+, B-, B, etc.... I
am shading the cell red if the number or letter decreases, green if it
increases and yellow if it stays the same. Since A is suppose to follow A+
and be higher in ranking than A- it should show green if A- was in the cell
above it in the column and it doesn't it shows red as if it were a worse
rating. Can someone help me figure this out???? Thanks in advance for your
time.
 
T

Tom Ogilvy

Setup a table on another sheet that assigns a numerical score to each letter
combination

A+ 99
A 98
A- 97
B+ 96
B 95
B- 94
C+ 93
C 92
C- 91

then in you conditional formula instead of referencing the cell with a
letter rating directly, use vlookup to change it to a value

assume D20 and D21 have letter ratings

=If(vlookup(D21,Table,2,False)>Vlookup(D20,Table,2,False),"Up","Down")

as an example usage. Hopefully you can incorporate that into your formulas.
 
G

Gary Rowe

You could try creating a custom list to sort by. Click on Tools/Options and
the Custom List tab. Enter a new list in the sequence you want and then when
you sort select the custom list.
Gary Rowe
 
C

craftwoodman

Gary,
Thanks for the tip, but I don't need to sort. I tried this and it doesn't
do what I need. What I am trying to do is color code a cell based on what is
entered into it. For example: if cell A1 has an A- in it and I enter an A in
cell A2, the color of the cell should change to green since the letter went
up in value. What is happening is excel recognizes the following sequential
order: A+, A-, A. So in this scenario cell A2 would have actually turned red
since excel's order is different. I need to create or change something so
that cell A2 turns green. I am trying to create a table, but am not having
much luck at it.
 
C

craftwoodman

Tom,
Thanks for the tip, but I consider myself a little more than a novice when
it comes to excel. I have tried to create a table on a seperate worksheet,
but I must be doing something wrong, because when I do this and do the
conditional formatting, nothing happens. I must be doing something wrong.
 
R

Ragdyer

Let's try this:

InY1 to Y9, enter this:
A+,A,A-,B+,B,B-,C+,C+C-

In Z1 to Z9, enter this:
9,8,7,6,5,4,3,2,1

This sets up your datalist.

Now, start by clicking in A2.
Then <Format> <ConditionalFormat>
Change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)>VLOOKUP(A1,$Y$1:$Z$9,2,0)
Set the format to Green and Bold, then <OK>,

Then click the ADD button to enable the entry of condition #2
Again, change "CellValueIs" to "Formula Is",
And enter this formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)=VLOOKUP(A1,$Y$1:$Z$9,2,0)
*NOTE* - same as first formula except ">" changed to "="
Set the format to Gold and Bold, then <OK>,

Then click the ADD button again to enable the entry of condition #3
Again, change "CellValueIs" to "Formula Is",
And enter this same formula:

=VLOOKUP(A2,$Y$1:$Z$9,2,0)<VLOOKUP(A1,$Y$1:$Z$9,2,0)
With the CHANGE here being from "=" to "<"
Set the format to Red and Bold, then <OK>, <OK>.

You've set the format for A2.

Now, click in A2, then click on the "FormatPainter" icon on the toolbar
(yellow paintbrush),
Click in A3, and drag down to copy as needed, to set the same format for the
rest of the cells in Column A.
 
C

craftwoodman

Ragdyer,
I want to thank you for this help. IT WORKED. You can't believe how much
time you are going to save me. I can't say thanks enough. Have a great new
year.
 
Top