Conditional Formatting for Numbers Followed by Text

Hi All

Hoping someone can point me in the right direction.
My wife is a teacher and for some reason only known to education they insist on using numbers followed by a plus (+) or minus (-) sign.
e.g. 1+; 5-; etc.

She then has to compare and highlight the greater or lesser value depending on the purpose of the data.
So if 1 child is awarded 1 another could be awarded 1+ which in the world of education is then higher than 1.
or 5 and 5- where 5- is considered lower than 5.
Why they don't just work on a scale of 1-10 or 1-20 only education knows.
The obvious complication here is that excel treats the trailing symbol as text.

I would like to be able to create a conditional format whereby I can compare the values in one column, say A1 with A2 and highlight the greater or smaller number assuming:
A1=1 and A2=1- or A2=1+

If anyone has managed this I would be massively grateful as this process takes ages when done manually especially when there are 300 or 400 kids.

Thanks for now

The ideal way to handle this would be to use a table which converts arbitrary labels into ordinal integers. If 1+ were best, 5- worst, best to insert a new worksheet, enter the following table in A1:B15 in that worksheet.

	A	B
1	1+	1
2	1	2
3	1-	3
4	2+	4
5	2	5
6	2-	6
7	3+	7
8	3	8
9	3-	9
10	4+	10
11	4	11
12	4-	12
13	5+	13
14	5	14
15	5-	15

Name this range CNVTBL, sort it in ascending order on column A (note that Excel considers numeric 1 and text "1" to be different values), and use lookups to convert[1-5]{|+|-} grades into ordinals.

Use this in conditional formatting like so: with grades in C3:C27, use conditional formatting formula criteria like =LOOKUP(C3,CNVTBL)=MIN(LOOKUP(C$3:C$27,CNVTBL)).

As a general rule, lookup conversion tables are the most robust way to convert arbitrary grade labels into a numeric scale.

Picky: I used LOOKUP rather than VLOOKUP because LOOKUP is more reliable when given array/range 1st arguments.

Main point is that CNVTBL's 2nd column values could be anything which makes sense. They could have been {98;95;92;89;85; . . . }. 2-column lookup tables are more flexible than using MATCH.

Hi All

Thank you very much for all the help so far.

Sorry my replies have been a bit slow. Long day on the main job.
I realised I didn't explain very well and I forgot to attach my in progress template. It was late :D
Now attached.

Basically the kids are given a predicted a grade in 1 column (O) and then in the next column (P) they are given their actual grade.
The teacher then has to highlight those achieving above their grade in green and those achieving below their predicted grade in red.
I've put the colours in manually to illustrate.
Apparently this ridiculous series of grades was concocted by either the government of one of their clown consultants.
So if you aren't quite achieving grade 1 then you get 1- and if you are just over but not quite 2 then it's 1+ all the way up to 9+

I can't quite believe this is the best that they could come up with.

That is absolutely brilliant thank you. Honestly I can't thank you enough. Neither can a whole department of teachers

I can only hope.