How do you give text a value?

[lylolil]

Hi there,

This is my first post so please bear with me,

I have produced an excel document to track pupil progress at school. The pupils are offered levels which go up in sub categories of a,b and c. so for example a pupil can start a subject at a level 4c and progress up to a 4a (the level after that being a 5c)

I have a column which is highlighting whether a pupil is working on, above or below their predicted grade but excel seems to think that a 4c is a higher grade than a 4a and therefore is giving out the wrong information. Does anyone know how I can tell excel the order in which the grades work in Excel 2010?

Thanks for any help.

[davegugg]

Excel is evaluating the letters as described in the help for the different Option Compare Statement options. If you pull up the help, you will see:

If used, the Option Compare statement must appear in a module before any procedures.

The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.

Option Compare Binary results in string comparisons based on a sort order derived from the internal binary representations of the characters. In Microsoft Windows, sort order is determined by the code page. A typical binary sort order is shown in the following example:

A < B < E < Z < a < b < e < z < À < Ê < Ø < à < ê < ø


Option Compare Text results in string comparisons based on a case-insensitive text sort order determined by your system's locale. When the same characters are sorted using Option Compare Text, the following text sort order is produced:

(A=a) < ( À=à) < (B=b) < (E=e) < (Ê=ê) < (Z=z) < (Ø=ø)

My suggestion would be to come up with a new way to rank your pupils, Maybe 3.1 and 3.3 instead of 3a and 3c.

[ConneXionLost]

With Excel, there are many ways to approach a problem, but knowing some of the specifics will help to narrow the search. Any chance you could post a sample workbook?